I have written a PowerShell script to “walk” the security structure of each SQL instance to find:
This is not an “all encompassing” look at every piece of security, but it does give a pretty comprehensive look at who has access to what. I wrote a script because gathering this information manually would be very, very time consuming. As far as I know, there is no function in SQL Server to collect all this information into one place. I guess I could have used a series of TSQL scripts to pull the info out by other means, but I am on a PowerShell kick, and I wanted to do it this way.
Besides, using my method, you can automate the process and audit multiple instances at once. Sound like fun?
I wrote this quick-and-dirty so there is no error handling. There is, of course, no warranty expressed or implied and you should use caution when running this (or any other script you find on the Internet). That said, all of my calls to SQL are read-only, so I don’t think you’ll have any problems.
I’ll go ahead and paste the script and then explain it below:
# =================================================================================
#
# NAME: InventorySQLUserSecurity.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.
# =================================================================================
# =================================================================================
#
# Declare Functions
#
# =================================================================================
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 “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.”}
}
# This is commented out to make the output less wordy.
#else
#{Write-host $spc10 “None.”}
} # Next user in database
}
# This is commented out to make the output less wordy.
#else
#{write-host $spc10 “InventoryUserSecurity.PS1: Error connecting to database ” $db.name “. Skipping to next database.”}
}
# =================================================================================
#
# Main Program Starts Here
#
# =================================================================================
[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
[string] $spc5 = “ ”
[string] $spc10 = “ ”
foreach ($SQLsvr in get-content “C:\SomePath\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
foreach ($SQLLogin in $SQLLogins)
{
write-host “———————————————————————————”
write-host “—– Login: ” $SQLLogin.name
write-host “———————————————————————————”
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:”
# $SQLLogin.EnumDatabaseMappings(): this line of code first checks to see if the login is mapped to any databases.
# If not, it won’t bother running through all the different databases. This results in a cleaner output,
# but it does slow things down a bit.
if ($SQLLogin.EnumDatabaseMappings())
{Write-host ” ”
foreach ( $DB in $svr.Databases)
{
GetDatabaseUser($DB)
} # Next Database
}
Else
{Write-host $spc10 “None.”}
} # Next Login
} # Next Server
The basic flow of the script is:
The output of this is a somewhat-formatted and fairly detailed look at what each person has access to. I’m sure I could have done this a myriad of other ways, but I was in a hurry.
Note that, depending on the number of logins and databases, this could chug for quite a while and output a lot of information (one instance I audited gave a 374-page report). The output is dumped to the console, which in PowerShell ISE is not a problem because it has a very large buffer. The next version of this will have the option to write to a text file, which isn’t much more difficult; I just didn’t have the reference in front of me on how to do that. So what I’ve been doing is copy/pasting the text into Word and finessing it from there.
My next version of this will probably be database-connected. I have been working on a PowerShell-based solution to inventory all SQL Servers on a network, including information on Hosts, SQL instances, Databases, Filegroups, and files; the end result is to have a comprehensive look at who owns what data and what resources the Business Units are consuming (e.g. Do the HR division’s databases take up more disk space than the Administration division’s databases?) This security audit data will be grafted into the database. If anyone’s interested I can write about that, too.
moffitar