Exploring permissions with sp_DBPermissions and sp_SrvPermissions : TSQL Tuesday 101

Kenneth Fisher, 2018-04-10

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads