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.