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 11

  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.

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.