Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

sp_SrvPermissions & sp_DBPermissions V4.0

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 every database a user has access to and what access they have.
  • 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: Below are the latest additions. My personal favorite is the new ALL option for the DBName parameter. 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
– 11/18/2013 – Corrected bug in the order of the parameters for sp_addsrvrolemember
and sp_dropsrvrolemember, also added parameter names both.
– 01/09/2014 – Added an ORDER BY to each of the result sets. See above for details.

sp_DBPermissions
– 11/18/2013 – Added parameter names to sp_addrolemember and sp_droprolemember.
– 11/19/2013 – Added an ORDER BY to each of the result sets. See above for details.
– 01/04/2014 – Add an ALL option to the DBName parameter.


Filed under: Dynamic SQL, Microsoft SQL Server, Security, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: code language, database permissions, dynamic sql, language sql, microsoft sql server, security, server permissions

Comments

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

Loading comments...