Documenting SQL Server Access – Who Can Do What?

Digby! says what?

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:

** 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.

 

 

Comments 13

  1. Pingback: Documenting SQL Server Access – Who Can Do What? - Syndication – WaterOx Consulting - SQL Server - SQL Server - Toad World

  2. Pingback: Documenting SQL Server Security | Senior DBA

  3. Thanks a lot !! For your script and I need your help to know how to find the inactive accounts in the sql server ( I mean i have to do cleanup of the logins who have left the company).

    1. Post
      Author

      If you are looking for domain accounts there should be a flag, or working with your sysadmins you can identify those accounts that should not have access and then deactivate or remove them.
      When it comes to SQL Logins the thing to remember is that there may be processes using those logins. I had to deal with this a LOT this past year where a former DBA created a lot of jobs tied to their account. if we deactivated the account everything blew up. It took months of picking and digging to get all the logins, aliases, proxies and credentials sorted out.
      I haven’t had time to dig, but I’m sure you can look in AD to determine the last time an account has logged in and similar within the SQL server master DB as well.
      In SQL Server you can query the sys.syslogins table to see that last access date for each login/account.

  4. Pingback: Audit account access on SQL server with powershell | Scompanion

  5. thank you so much!  its a real life saver.  instead of write host, how could I get this into an excel sheet in row format?

    1. Post
      Author

      To get this to an excel sheet you could write to a file and then import it. I am working to get sa new script tool released that may help with the export issue. Keep an eye out for it!

  6. Thanks a lot for the script.

    I am having an issue with AD Groups that have a space in the name.

    If I run the get-adgroup command manually with “” around the AD Group it runs fine.  I just can not figure out how to edit the script to add the same functionality.

    Any help would be greatly appreciated.

  7. Hi!, could you please tell me wich would be the minimun privileges required to run the scripts for the internal audit department?. Thanks!

     

    FC

    1. Post
      Author
  8. This would be awesome if it went to a csv file. I think I can figure out how to do that little bit. I almost wrote one of these myself so this saved me a bunch of time.

  9. I am looking for a script that displays what accounts (local and/or domain) are in SQL server roles.  Do you have something like this?  I am using below script but it overwrites the files.

    Import-Module SQLPS -disablenamechecking

    ## – BUild list of Servers manually (this builds an array list):
    $SQLServers = “wsqlfarm01d”,”wsqlfarm01p”,”wcnssql02p”;
    $SysAdmin = $null;
    $bulkadmin = $null;
    $dbcreator = $null;
    $diskadmin = $null;
    $processadmin = $null;
    $securityadmin = $null;
    $serveradmin = $null;
    $setupadmin = $null

    foreach($SQLSvr in $SQLServers)
    {

    ## – SysAdmin server role:
    $MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;
    $SQLLogins = $MySQL.Logins;

    $SysAdmin += foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘sysadmin’)
    {
    Write-Host “SysAdmin found: $($SQLUser.Name)” -ForegroundColor Yellow;
    $SQLUser | Select-Object
    @{label = "SQLServer"; Expression = {$SQLSvr}},

    @{label = “CurrentDate”; Expression = {(Get-Date).ToString(“yyyy-MM-dd”)}},
    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $sysadmin | Export-Csv -Path ‘C:\temp\SQLSysAdminList.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLSysAdminList.csv’;

    #### -- BulkAdmin server role

    $bulkadmin = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘bulkadmin’)
    {
    Write-Host "bulkadmin found: $($SQLUser.Name)" -ForegroundColor Green;
    $SQLUser | Select-Object

    @{label = “SQLServer”; Expression = {$SQLSvr}},
    @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}},

    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $bulkadmin | Export-Csv -Path ‘C:\temp\SQLBulkAdminList.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLBulkAdminList.csv’;

    #### — dbcreator server role

    $dbcreator = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘dbcreator’)
    {
    Write-Host “dbcreator found: $($SQLUser.Name)” -ForegroundColor Green;
    $SQLUser | Select-Object
    @{label = "SQLServer"; Expression = {$SQLSvr}},

    @{label = “CurrentDate”; Expression = {(Get-Date).ToString(“yyyy-MM-dd”)}},
    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $dbcreator | Export-Csv -Path ‘C:\temp\SQLdbcreator.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLdbcreator.csv’;

    #### -- diskadmin server role

    $diskadmin = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘diskadmin’)
    {
    Write-Host "diskadmin found: $($SQLUser.Name)" -ForegroundColor Green;
    $SQLUser | Select-Object

    @{label = “SQLServer”; Expression = {$SQLSvr}},
    @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}},

    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $diskadmin | Export-Csv -Path ‘C:\temp\SQLdiskadmin.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLdiskadmin.csv’;

    #### — processadmin server role

    $processadmin = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘processadmin’)
    {
    Write-Host “processadmin found: $($SQLUser.Name)” -ForegroundColor Green;
    $SQLUser | Select-Object
    @{label = "SQLServer"; Expression = {$SQLSvr}},

    @{label = “CurrentDate”; Expression = {(Get-Date).ToString(“yyyy-MM-dd”)}},
    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $processadmin | Export-Csv -Path ‘C:\temp\SQLprocessadmin.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLprocessadmin.csv’;

    #### -- securityadmin server role

    $securityadmin = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘securityadmin’)
    {
    Write-Host "securityadmin found: $($SQLUser.Name)" -ForegroundColor Green;
    $SQLUser | Select-Object

    @{label = “SQLServer”; Expression = {$SQLSvr}},
    @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}},

    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $securityadmin | Export-Csv -Path ‘C:\temp\SQLsecurityadmin.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLsecurityadmin.csv’;

    #### — serveradmin server role

    $serveradmin = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘serveradmin’)
    {
    Write-Host “serveradmin found: $($SQLUser.Name)” -ForegroundColor Green;
    $SQLUser | Select-Object
    @{label = "SQLServer"; Expression = {$SQLSvr}},

    @{label = “CurrentDate”; Expression = {(Get-Date).ToString(“yyyy-MM-dd”)}},
    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $serveradmin | Export-Csv -Path ‘C:\temp\SQLserveradmin.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLserveradmin.csv’;

    #### -- setupadmin server role

    $setupadmin = foreach($SQLUser in $SQLLogins)
    {
    foreach($role in $SQLUser.ListMembers())
    {
    if($role -match ‘setupadmin’)
    {
    Write-Host "setupadmin found: $($SQLUser.Name)" -ForegroundColor Green;
    $SQLUser | Select-Object

    @{label = “SQLServer”; Expression = {$SQLSvr}},
    @{label = "CurrentDate"; Expression = {(Get-Date).ToString("yyyy-MM-dd")}},

    Name, LoginType, CreateDate, DateLastModified;
    };
    };
    };

    $setupadmin | Export-Csv -Path ‘C:\temp\SQLsetupadmin.csv’ -Force -NoTypeInformation;
    ii ‘C:\temp\SQLsetupadmin.csv’;

    ## – End of Code block

    }

    1. Post
      Author

      The most simple code I can figure for SQL roles and members would be run on the SQL Server itself. Note, this ain’t pretty but shows where the info is.
      USE master

      SELECT DP1.name AS ServerRoleName,
      isnull (DP2.name, 'No members') AS ServerUserName
      FROM sys.server_role_members SRM
      RIGHT OUTER JOIN sys.server_principals AS DP1
      ON SRM.role_principal_id = DP1.principal_id
      LEFT OUTER JOIN sys.server_principals AS DP2
      ON SRM.member_principal_id = DP2.principal_id

      EXEC sp_msforeachdb
      'use ?;
      SELECT DB_Name(), DP1.name AS DatabaseRoleName,
      isnull (DP2.name, ''No members'') AS DatabaseUserName
      FROM sys.database_role_members AS DRM
      RIGHT OUTER JOIN sys.database_principals AS DP1
      ON DRM.role_principal_id = DP1.principal_id
      LEFT OUTER JOIN sys.database_principals AS DP2
      ON DRM.member_principal_id = DP2.principal_id
      WHERE DP1.type = ''R''
      ORDER BY DP1.name;'

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.