Community Pick: Many members of our community have endorsed this article.

How to Tell Who Has Access to What in SQL Server

Greg BurnsGreg Burns (poortatey)
Published:
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:  

      Each Login (Windows or SQL)
            * Its Server Roles
            * Every database to which the login is mapped
            * The associated "Database User" for this login
                  * Its Database roles
                  * Any explicitly assigned permissions

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 T-SQL 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.  


The code (explanation to follow below):
# ======================================================================================================================
                      #
                      # 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

Open in new window


 
The basic flow of the script is:

* Open the Instances.txt file, which is a simple text file with one instance per line.
      * The script will loop through each of the instances and prepare a report for each of them.
* Provide some basic information about the instance.
* For each Instance:
      * Connect to the instance's "Logins" collection.
            * For each Login:
                  * Provide basic information about the login:
                  * Whether it is SQL or Windows security
                  * When the login was created
                  * What the login's default database is
                  * Whether or not the login currently has database access (i.e., disabled or not)
                  * List any server roles
                  * Find if the Login is associated with any database users.
                  * If the Login has any database users (i.e., the $SQLLogin.EnumDatabaseMappings() is not null) then:
                  * For Each Database:
                        * If the database is online (i.e., its status is "Normal") then:
                        * Show the SQL Login and its associated Database Username
                        * When it was created/modified
                        * What its default schema is
                        * Whether or not it has access to the database (i.e., denied or granted)
                        * The Database user's DB roles
                        * Any explicitly granted permissions (e.g., to tables or stored procedures)
                  * Next Database
            * Next Login
      * Next SQL instance

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.

The next iteration of this solution is 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 data is all added dynamically to an inventory database from which reports can be run.  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.
4
5,406 Views
Greg BurnsGreg Burns (poortatey)

Comments (1)

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
poortatey:

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

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.