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 V5.0

These are a couple of stored procedures I wrote to help me with security research. Each of the stored procedures 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 of the datasets has a set of do/undo scripts as well as various useful columns. For example the second data set contains information about which logins/users belong to which roles; and scripts to either add or remove the login/user from that role.

The stored procedures also have 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 more easily call them from any database on the instance.

A few examples of times I’ve found them particularly 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 login across all databases.
  • 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.

 
Standard disclaimers: This code is as-is. You should be careful running code you haven’t reviewed on your systems. Feel free to use it and place it on your systems. Please do not post my code without giving proper credit and preferably back to the original page. And of course if you happen to notice a problem, or have a suggestion please post them here or email me and I’ll be glad to fix/add as appropriate.

Latest update: Below are the latest additions.

sp_SrvPermissions

  • 04/27/2014 – Add @DBName parameter.

sp_DBPermissions

  • 4/29/2014 – Fix: Removed extra print statements
  • 4/29/2014 – Fix: Added SET NOCOUNT ON
  • 4/29/2014 – Added a USE statement to the scripts when using the @DBName = ‘All’ option
  • 5/01/2014 – Added @Permission parameter
  • 5/14/2014 – Added additional permissions based on information from Kendal Van Dyke’s post

    http://www.kendalvandyke.com/2014/02/using-sysobjects-when-scripting.html

  • 6/02/2014 – Added @LoginName 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...