<

[Webinar] Streamline your web hosting managementRegister Today

x

How to Tell Who Has Access to What in SQL Server

Published on
11,169 Points
4,769 Views
4 Endorsements
Last Modified:
Approved
Community Pick
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 61

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month