<

Go Premium for a chance to win a PS4. Enter to Win

x

How to Tell Who Has Access to What in SQL Server

Published on
11,113 Points
4,713 Views
4 Endorsements
Last Modified:
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
Comment
Author:Greg Burns
1 Comment
 
LVL 60

Expert Comment

by:Kevin Cross
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month