Documenting SQL Server Security

passwords

As a Database Administrator, one of your primary responsibilities is making sure the people who need to access our data can get to the data at all hours of day and night. But that also means making sure the people who should not have access never have access at any time. SQL Server gives you the tools to assign permissions to the instance and each database, but do you have the ability see those settings and do a periodic review of the permission and security settings?

In this article by Chris Bell we see a PowerShell script that will output the current security settings for your review.

 # ======================================================================================
 #
 # 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
 #
 # 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 = "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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s