Exploring permissions with sp_DBPermissions and sp_SrvPermissions : TSQL Tuesday 101

1

April 10, 2018 by Kenneth Fisher

T-SQL Tuesday Our host for T-SQL Tuesday this month is Jens Vestergaard (b/t) and he has asked about our favorite SSMS tool. My initial thought was to talk about using solutions in SSMS but I’d already written about that. My next thought was to write about sp_DBPermissions and sp_SrvPermissions which of course I’ve written about several times. No big surprise, I wrote them after all.

So what tool am I going to write about? Well, sp_DBPermissions and sp_SrvPermissions of course. I mean I did write them after all, and Jens did say we could brag about something we wrote :).

Basic background if you didn’t already know. I get a lot, and I mean a lot of requests related to security. The GUI is rather cumbersome but the system tables require some scripting to get useful information. So over time I wrote up a couple of scripts and kept changing the WHERE clause as needed. Over time I decided to formalize them a bit and share. I’ve been doing my best to add to them here and there (although maybe not as often as I should) and ended up with a tool I’m quite proud of. I mean it’s not sp_whoisactive but then again it’s a whole different purpose.

So what can you use them for? Well, currently I have three major uses.

Research

Possibly the most common use is just simple research. For example today I was asked

  • What databases does this AD group have access to?

 
Simple enough:

exec sp_DBPermissions 'All', @LoginName = 'Giant'

Couple of points of note.

  • The first parameter is the database you are interested in. Unless you use All which means All of the databases.
  • All of the research parameters (Name, LoginName, Type, etc) are LIKE parameters. You can pass in any form of pattern you like and a % is automatically put on each end of the string. There is an optional parameter to turn this off.

 

Scripting

About a week ago I was asked to duplicate a set of permissions from one AD group to another. The first thing I do is to do a search just like above. Once I have what I need I add the Output parameter.

exec sp_DBPermissions 'All', @LoginName = 'Giant', @Output = 'CreateOnly'

You can use CreateOnly, DropOnly, and ScriptsOnly depending on your needs, and then it’s easy enough to copy and paste into a query window and do some replaces to replace the old name with the new one.

This is also a good way to generate scripts before a move of some type.

Reporting

One of the most recent things I added was a simple set of audit reports. Using the same parameter Output we can get a nice and simple report that can be passed off to auditors.

exec sp_DBPermissions 'All', @LoginName = 'Giant', @Output = 'Report'


I end up using these scripts at least once a day, frequently quite a bit more often. Hopefully, they’ve been as useful to others as it has been to me.

One thought on “Exploring permissions with sp_DBPermissions and sp_SrvPermissions : TSQL Tuesday 101

  1. […] Kenneth Fisher wants you to know about your SQL Server’s permission setup: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,469 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: