When is the last time you audited who has SQL Server access?
No really? When did you last do this? Have you ever done it?
If you have, great! If not, well, let’s fix that.
Below I have a PowerShell script that can help enlighten you to your SQL Server security situation a little more.
Before we get to that, let me tell you a little about it.
At a current client I needed to do a security audit, but without any outside 3rd party purchased tools to help.
That gets tricky, especially when dealing with larger enterprise systems and a possibly lax approach to who can access the data.
To solve this situation I started digging around for a script that could possibly help me. I had some, but nothing that really dug in as deep as I would like. The best I could find was created by Greg Burns and can be snagged from his post titled:
Audit All SQL User Security with PowerShell
Great! Less that I have to do. I grabbed the script, read through it, then ran it (We all know not to blindly run scripts from the internet right?)
Nice. It gave some good info back. This was going to be even easier.
Then as picking through it I found some things it wasn’t doing and that was the inspiration for this evolution of the script.
I wanted the script to do a few things. Tell me who is in a AD Group that was granted rights. IT is one thing to see the group name, but that doesn’t really tell me who has access. I also wanted the output to be a little more user readable, so I formatted the output some. There are other things I did too, but you can fun reading through the code and comparing the 2 sources.
How do I use this script?
That’s the simple part. This is a PowerShell script so there are lots of ways to use it.
I myself run it in PowerShell ISE and copy paste the output to MSWord. I then run some quick find-replace to highlight things that matter to me. Things like who is sysadmin, who is a DBO, & which are inactive accounts. (If you want to know how to do this, let me know in the comments and I’ll post about it too)
Prerequisites:
PowerShell Version:
I developed my version of this script in PowerShell 4 so I know it runs in that. I have not had the time to test this in earlier version of PowerShell.
If you do try it in an earlier version, let me know how it goes in the comments below.
Instances.txt
The script also references a text file stored in c:\temp\ (you can change that!) named instances.txt.
Guess what it contains! Yep, a list of the instances that we want this script to run against.
I am not including that in the download because if you are looking to use a script like this, you should know how to make a simple text file with a list of instance names, one per line.
SQL Security Access
Go figure, a script that checks SQL Securty should be run as a user that has permissions to access the security information on each instance being checked. I used integrated security because it is easier to code. You could change the connection process to use a SQL login, but the integrated makes the AD checks easier too.
Read Only Permissions on the domain
When I developed and ran this I had an account that had read only access to the Active Directory so I could pull the details for any AD Groups granted permissions on the SQL instance. If you don’t have this you may get red errors when trying to list out the group members, but everything else should work.
That’s it! (AFAIK)
I kind of fell into the same situation as Greg in not having enough time to really dump this to a file instead of the output, or tie it to a SQL table for the instance names, but feel free to do that. I’ll probably get around to it at some point in time too.
Alright! Where is this script?
Right here in copy/paste format:
# ========================================================================================================
#
# NAME: WOX_Permissions.ps1
#
# This script can be used to step through the various levels of security on each of your SQL Server instances.
# It is recommended to run this script with a domain account that can access the AD to collect group information.
# Thanks go to Greg Burns whose original post here: http://www.alaskasql.org/Blog/Post/35/Audit-All-SQL-User-Security-with-PowerShell
# inspired this evolution.
#
# This script returns:
# - The Server Name, Versionm, edition and Login mode
# - For each Login found on the SQL Server instance
# - Name
# - Type - SQL or Windows
# - Create date
# - Default DB
# - Disabled?
# - Server role (in Red if sysadmin)
# - If Type is a Windows AD group each member's name and Login
# - Database roles
# - Any explicitily granted permissions
#
# The script runs based on a list of server names located in c:\temp\instances.txt.
# Named instances can be listed as Hostname\instancename.
# The text file should contain one instance per line.
# All output is to the console (Formatting was easier).
#
# (C) 2016, WaterOx Consulting, Inc.
# See https://WaterOxConsulting.com/eula for the End User Licensing Agreement.
#
# ========================================================================================================
Function GetDBUserInfo($Dbase)
{
if ($dbase.status -eq "Normal") # ensures the DB is online before checking
{$users = $Dbase.users | where {$_.login -eq $SQLLogin.name} # Ignore the account running this as it is assumed to be an admin account on all servers
foreach ($u in $users)
{
if ($u)
{
$DBRoles = $u.enumroles()
foreach ($role in $DBRoles) {
if ($role -eq "db_owner") {
write-host $role "on"$Dbase.name -foregroundcolor "red" #if db_owner set text color to red
}
else {
write-host $role "on"$Dbase.name
}
}
#Get any explicitily granted permissions
foreach($perm in $Dbase.EnumObjectPermissions($u.Name)){
write-host $perm.permissionstate $perm.permissiontype "on" $perm.objectname "in" $DBase.name }
}
} # Next user in database
}
#else
#Skip to next database.
}
#Main portion of script start
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null #ensure we have SQL SMO available
foreach ($SQLsvr in get-content "C:\temp\Instances.txt") # read the instance source file to get instance names
{
$svr = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLsvr
write-host "================================================================================="
write-host "SQL Instance: " $svr.name
write-host "SQL Version:" $svr.VersionString
write-host "Edition:" $svr.Edition
write-host "Login Mode:" $svr.LoginMode
write-host "================================================================================="
$SQLLogins = $svr.logins
foreach ($SQLLogin in $SQLLogins)
{
write-host "Login : " $SQLLogin.name
write-host "Login Type : " $SQLLogin.LoginType
write-host "Created : " $SQLLogin.CreateDate
write-host "Default DB : " $SQLLogin.DefaultDatabase
Write-Host "Disabled : " $SQLLogin.IsDisabled
$SQLRoles = $SQLLogin.ListMembers()
if ($SQLRoles) {
if ($SQLRoles -eq "SysAdmin"){ write-host "Server Role : " $SQLRoles -foregroundcolor "red"}
else { write-host "Server Role : " $SQLRoles
} } else {"Server Role : Public"}
If ( $SQLLogin.LoginType -eq "WindowsGroup" ) { #get individuals in any Windows domain groups
write-host "Group Members:"
try {
$ADGRoupMembers = get-adgroupmember $SQLLogin.name.Split("\")[1] -Recursive
foreach($member in $ADGRoupMembers){
write-host " Account: " $member.name "("$member.SamAccountName")"
}
}
catch
{
#Sometimes there are 'ghost' groups left behind that are no longer in the domain, this highlights those still in SQL
write-host "Unable to locate group " $SQLLogin.name.Split("\")[1] " in the AD Domain" -foregroundcolor Red
}
}
#Check the permissions in the DBs the Login is linked to.
if ($SQLLogin.EnumDatabaseMappings())
{write-host "Permissions:"
foreach ( $DB in $svr.Databases)
{
GetDBUserInfo($DB)
} # Next Database
}
Else
{write-host "None."
}
write-host " ----------------------------------------------------------------------------"
} # Next Login
} # Next Server
** minor edit – Thanks to Carlton Ramsey (B|T) for catching that I was cutting the 3 character domain name off rather than generically catching the slash. Code has been updated to reflect this. Also removed the file download until I can get the file itself fixed and uploaded again.
Minor edit #2 -> changed an = to -eq when checking for sysadmin permissions. Now the script lists all the server roles the user/group is in instead of just showing syadmin if there.