sp_SrvPermissions & sp_DBPermissions V3.0

Leave a comment

November 4, 2013 by Kenneth Fisher

These are a couple of stored procedures I wrote to help me with security research. Each sp returns three data sets.

  1. A list of principals and some basic properties about them.
  2. Role membership
  3. Object/Database/Server level permissions

 
Each row of each dataset has not only the appropriate properties but a set of do/undo scripts. For example a script to add someone to a role, or remove them for a role, grant them a permission, revoke the permission from them.

Last but not least each sp has a number of parameters for restricting the result sets. For example principal name, role name, principal type, object name etc.

These sp’s can be run/stored anywhere and work just fine but if you run them in master then you can call them from any database on the instance.

Examples of times I’ve found them handy:

  • I need to know all permissions for a given user.
  • I need to copy a login from one server to another (with SID and password).
  • I need to know everyone who has permissions to a specific object in the database.
  • I need to know everyone who is a member of sysadmin.

 
Latest update: First of all I’ve moved these SPs to their own pages so that all of the links will point to a single location. Beyond that there a few additions, and of course if you happen to notice a problem, or have a suggestion please post them here and I'll be glad to fix/add as appropriate.

sp_SrvPermissions
V3.0 Changes
10/5/2013 – Added @Type parameter to pull only principals of a given type.
10/20/2013 – Remove SID in CREATE LOGIN script from v2005 and lower since it requires a special function to convert from binary to varchar.

sp_DBPermissions
V3.0 Changes
10/5/2013 – Added @Type parameter to pull only principals of a given type.
10/10/2013 – Added @ObjectName parameter to pull only permissions for a given object.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 2,146 other followers

Follow me on Twitter

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