sp_SrvPermissions & sp_DBPermissions V6.0 Finally!

5

March 27, 2017 by Kenneth Fisher

It’s been almost 3 years since I updated these SPs! I can’t believe so much time has gone by! Well, I’m sure all of you have been holding your collective breath but I finally have some new updates. I’m particularly excited about the @DropTempTables and @Output options.

Just as a reminder they can be found in the drop down of the free scripts menu. Or here: sp_dbpermissions | sp_srvpermissions

New Features

  • @UserLikeSearch to turn off the LIKE searches. It’s on by default.
  • @IncludeMSShipped to exclude users/logins created by Microsoft. They are included by default.
  • @DropTempTables to keep the temp tables (dropped by default) that the data is stored in. This way individuals and automated process can collected the data and process it. The temp tables are:
    • ##[DB/Srv]Principals
    • ##[DB/Srv]Roles
    • ##[DB/Srv]Permissions
  • @Output allows for new output options.
    • None – No output at all. This is mostly meant to be used with when the plan is to collect data from the temp tables.
    • CreateOnly – Return only the create scripts.
    • DropOnly – Return only the drop scripts.
    • ScriptsOnly – Return both the create and drop scripts.
    • Default – Normal behavior.

Other changes

  • A couple of bug fixes.
  • Added IF EXISTS to the principal (user/logins) drop and create scripts.
  • Moved SID towards the end of the list (leaving create & drop scripts at the end) since most of the time you don’t really need the SID.
  • Remove the create/drop scripts for the users (sp_DBPermissions) guest, public, sys and INFORMATION_SCHEMA

Quick intro if you haven’t seen these SPs before. They 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.

5 thoughts on “sp_SrvPermissions & sp_DBPermissions V6.0 Finally!

  1. […] Kenneth Fisher ( blog | twitter ) has got SrvPermissions & DBPermissions. […]

  2. Mark Anderson says:

    looks interesting, but where on earht are they?

    • Heh, sorry about that. Usually I add direct links. I’ll take care of that in a little bit. In the mean time if you look under the “FREE SCRIPTS” menu you will see links for each of them.

  3. […] they grew into the procedures sp_dbpermissions and sp_srvpermissions. I recently updated them to v 6.0 and one of the main reasons was so I could add an output option. And the main reason for that was […]

  4. Albert says:

    Hi , Great Scripts.
    But I have found an issue, when you run sp_DBpermissions @DBName =’ALL’ in a server with Databases in an AlwaysOn Group that are the replica in the server and are configured in ReadOnly mode.

    “Msg 978, Level 14, State 1, Line 17
    The target database (‘TEST_AG’) is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online.”

    To solve this, I have changed the lines like

    FOR SELECT name FROM sys.databases  ORDER BY name

    By

    FOR SELECT d.name FROM sys.databases d 
    LEFT OUTER JOIN sys.dm_hadr_availability_replica_states hars 
         ON d.replica_id = hars.replica_id 
    WHERE ISNULL(hars.role_desc, 'PRIMARY') = 'SECONDARY' ORDER BY name

    And seems that it works fine.

    TIA

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 1,672 other followers

Follow me on Twitter

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