# ======================================================================================================================
#
# NAME: InventorySQLUserSecurity_DB.ps1
#
# Comment: This script is designed to "walk" the security structure of each SQL instance to find:
#
# · Each Login (Windows or SQL)
# o Its Server Roles
# o Every database to which the login is mapped
# § The associated "Database User" for this login
# · Its Database roles
# · Any explicitly assigned permissions
#
# The script is hard-coded to locate a text file with a list of instance names (Hostname\instancename format).
# The text file should contain one instance per line.
# All output is dumped to console.
# About the only error checking included is to check whether a database is online before analyzing it.
# Note: this script will ignore orphaned database users. Use the built-in stored procedure sp_change_users_login
# to identify DB users who are not associated with a SQL login.
# =======================================================================================================================
Function GetDatabaseUser($Dbase)
{
if ($dbase.status -eq "Normal")
{$users = $Dbase.users | where {$_.login -eq $SQLLogin.name}
foreach ($u in $users)
{
if ($u)
{
write-host $spc5 "===== Database: " $Dbase.Name
write-host $spc5 "Login's Database Mappings: "
#$u | select-object name, login, parent, createdate, datelastmodified, DefaultSchema, HasDBAccess
Write-host $spc10 "Name : " $u.Name
Write-host $spc10 "Login : " $u.Login
write-host $spc10 "Unique Identifier: " $u.SID
Write-host $spc10 "CreateDate : " $u.createdate
Write-host $spc10 "DateLastModified : " $u.datelastmodified
Write-host $spc10 "DefaultSchema : " $u.DefaultSchema
Write-host $spc10 "HasDBAccess : " $u.HasDBAccess
write-host $spc5 "Database Roles for this DBUser:"
$DBRoles = $u.enumroles()
if ($DBRoles)
{$spc10 + $DBRoles}
Else
{Write-host $spc10 "None."}
write-host $spc5 "Explicit Database Permissions for this DBUser:"
$DBExplict = $Dbase.EnumObjectPermissions($u.Name) | select-object objectname, permissiontype, permissionstate
if ($DBExplict)
{$spc10 + $DBExplict}
Else
{Write-host $spc10 "None."}
}
#else
#{Write-host $spc10 "None."}
} # Next user in database
}
#else
#{write-host $spc10 "InventoryUserSecurity.PS1: Error connecting to database " $db.name ". Skipping to next database."}
}
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[string] $spc5 = " "
[string] $spc10 = " "
foreach ($SQLsvr in get-content "C:\SQLInv_Scripts\Instances.txt")
{
$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLsvr
write-host "================================================================================="
write-host "-----"
write-host "----- SQL Instance: " $svr.name
write-host "-----"
write-host "================================================================================="
Write-host "SQL Version:" $svr.VersionString
Write-host "Edition:" $svr.Edition
Write-host "Login Mode:" $svr.LoginMode
$SQLLogins = $svr.logins
"Number of Logins: " + $SQLLogins.count
write-host " "
foreach ($SQLLogin in $SQLLogins)
{
write-host "---------------------------------------------------------------------------------"
write-host "----- Login: " $SQLLogin.name
write-host "---------------------------------------------------------------------------------"
write-host $spc5 "Unique Identifier: " $SQLlogin.SID
write-host $spc5 "Login Type: " $SQLLogin.LoginType
write-host $spc5 "Created: " $SQLLogin.CreateDate
write-host $spc5 "Default Database: " $SQLLogin.DefaultDatabase
write-host $spc5 "Has Access to this instance: " $SQLLogin.HasAccess
write-host " ----------------------------------------------------------------------------"
write-host $spc5 "Server Roles for:" $SQLLogin.name
$SQLRoles = $SQLLogin.ListMembers()
if ($SQLRoles)
{$spc10 + $SQLRoles}
else
{Write-host $spc10 "None."}
Write-host $spc5 "This login maps to database users in the following databases:"
if ($SQLLogin.EnumDatabaseMappings())
{Write-host " "
foreach ( $DB in $svr.Databases)
{
GetDatabaseUser($DB)
} # Next Database
}
Else
{Write-host $spc10 "None."}
} # Next Login
} # Next Server
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
Thanks for the tool!
I ran this on Windows XP after ensuring that I had enabled the scripts to run per:
http://technet.microsoft.com/en-us/library/ee176949.aspx
Very pleased to get my report after. Will come in handy at some point I am sure, plus gave me some cool PowerShell to play with. So for the practical and the fun, YES vote from me.
Regards,
Kevin