sp_DBPermissions

3

August 14, 2013 by Kenneth Fisher

Last week I posted a series of database permissions scripts. I’ve been using them quite a bit over the last week and decided they were too unwieldy the way they were. I had to copy over each script, put in each parameter and then run it. Not all that much work, but more than it needed to be. So I decided to combine them together into a single stored procedure sp_DBPermissions. Because it starts with sp_, if created in the master database it can then be called from any database. The majority of the details on how to run it are in the comments of the stored procedure itself, but to give a quick overview, by passing in a database name it will return security information on any database. The information can be narrowed down by passing in a principal and/or role name.

Of course any comments/bugs/suggestions for improvements you wish to make are more than welcome.

NOTE: I’ve removed the code and put in a link to the latest version.
sp_DBPermissions

About these ads

3 thoughts on “sp_DBPermissions

  1. DBAWaffle says:

    Hi ! Awesome script :) just gave it a go. Getting tables together to generate meaningful information regarding permissions (especially when the audience are non-DBAs) is difficult.

    I use this to gather a list of permissions against a database –

    select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, sch.name
    from sys .database_principals as prin
    inner join sys. database_permissions per
    on prin. principal_id = per .grantee_principal_id
    left outer join sys.schemas sch
    on per. major_id = sch .schema_id and prin. name ‘public’
    where class_desc = ‘SCHEMA’
    union
    select prin. name, prin .type_desc, per.class_desc , per. permission_name, per.state_desc, isnull( obj.name , ‘database_name’ )
    from sys .database_principals as prin
    inner join sys. database_permissions per
    on prin. principal_id = per .grantee_principal_id
    left outer join sys.objects obj
    on per. major_id = obj .object_id
    where class_desc ‘SCHEMA’ and prin. name ‘public’
    order by prin.name

    Interested to you know your thoughts :)

    • Not bad although I would recommend not excluding the public role. Every now and again (even frequently sometimes) you run into a database where permissions were granted to the public role. I can tell you from experience if you are trying to track down why someone has the access they do and forget to check the public role it can make things very frustrating.

  2. […] week I posted my stored procedure for database permissions, sp_DBPermissions. A couple of days later I was demonstrating it to one of my co-workers and his response was […]

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

Follow

Get every new post delivered to your Inbox.

Join 491 other followers

%d bloggers like this: