sp_DBPermissions
3August 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
Category: Dynamic SQL, Microsoft SQL Server, Security, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL | Tags: database permissions, dynamic sql, language sql, microsoft sql server, security, system functions
3 thoughts on “sp_DBPermissions”
Leave a comment Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
SQL Studies 
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.
[…] 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 […]