SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Exploring permissions with sp_DBPermissions and sp_SrvPermissions : TSQL Tuesday 101

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...