It’s been a while since I posted an updated to usp_DBPermissions and usp_SrvPermissions. Sorry. I got lazy with my posting.

Changes

sp_SrvPermissions
— V5.5
— 7/22/2014 – Changed strings to unicode
— V6.0
— 10/19/2014 – Add @UserLikeSearch and @IncludeMSShipped parameters.

sp_DBPermissions
— V5.5
— 7/15/2014 – Bunch of changes recommended by Robert Davis (b/t)
     Primarily changing the strings to unicode & adding QUOTENAME in a few places
     I’d missed it.
— V6.0
— 10/19/2014 – Add @UserLikeSearch and @IncludeMSShipped parameters.

New Parameters:

@UseLikeSearch
When this is set to 1 (the default) then the search parameters will use LIKE (and
%’s will be added around the @Principal and @Role parameters).
When set to 0 searchs will use =.
@IncludeMSShipped
When this is set to 1 (the default) then all principals will be included. When set
to 0 the fixed server roles and SA and Public principals will be excluded.

Some of the common uses I put these scripts to.

I’m not sure I’ve done this before but here are a few common uses I put these SPs to.

Moving a database from one server to another:
Every now and again I need to move a database from one instance to another. This isn’t a case where I’m copying an entire instance, just one DB.

EXEC sp_SrvPermissions @DBName = 'DBName'

You can then copy out the create script (last column, first dataset) for all of the logins associated with this database. Note: In SQL 2005 because the conversion of SID from varbinary to char doesn’t work properly the SID for SQL Logins won’t be correct and once you move the DB these Users will have to be re-associated with the Logins. The passwords however will be correct.

I need to add a user to an AD group that has read only access to a specific database.
This is a common one. I need to list out all of the AD groups and their permissions to see which one is appropriate.

EXEC sp_DBPermissions 'DBName', @Role = 'G'

After reviewing the role memberships and individual permissions I create a list of possible candidates. I then need to check each of these candidates to make sure they don’t have permissions elsewhere on the instance that might be a problem. For each login name from the first dataset of the previous output I run the following scripts and review the output of the second two datasets (role memberships and individual permissions):

-- Check what server level permissions the login might have.
EXEC sp_SrvPermissions 'LoginName'
-- Check for additional permissions in other databases
EXEC sp_DBPermissions 'All', @LoginName = 'LoginName'

The reason you want to use the @LoginName parameter here is to ensure you get all users that are tied to that login. Users, as you probably already know, do not always have the same name as the Login so it’s best to do it this way.

I need to create a new user with the same permissions as a different one.
Start by pulling the permissions of the source user.

EXEC sp_DBPermissions 'All', @LoginName = 'LoginName', 
     @UserLikeSearch = 0

Copy the create scripts from each dataset and paste them into a query window. Then do a search and replace and replace all occurances of [SourceUser] with [NewUser]. You now have a script that will create the users for each database, add them to the required roles, and grant them any individual permissions required. You still have to create the login manually of course.

This is the first example I’ve used @UserLikeSearch in. Usually I’d rather get extra results and review them manually, but in this case that defies the point of making it easy. Without using @UserLikeSearch if my login is called Test I’m going to pull every user associated with Test, MyTest and User_Test.

There are any number of other uses I’ve found over the last year or two but these are some of the more common ones. Feel free to make suggestions or let me know of any bugs you find in the comments.