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

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

List all permission in all databases

By Alejandro Pelc,

This is a very simple script that will create a table variable and fill it with all the users and roles permission for each object on all the databases (can be easily changed to get the permission of only one database or custom ones). The usage is really easy, just paste it on Management Studio and execute it.

The script creates three table variables (@dbs, @permission and @objects), one for storing the databases' names (@dbs), other to store the permission (@permission) and the third for storing the databases objects (@objects).

When the script runs, it will fill the @dbs with all the databases on the server, including snapshots. Then, it'll loop through the table, using the database name, to fill the @objects table. Finally, it'll join the @objects table with the sys.database_principals and sys.database_permissions to retrieve the permission and fill the @permission table. This operation will be performed on each database on the @dbs table. In case you want to get the permission for specific databases, just change the line that inserts the databases name from the sys.databases for the custom one.

Total article views: 14881 | Views in the last 30 days: 68
 
Related Articles
FORUM

Scripting Objects Including Permissions

Scripting Objects Including Permissions

FORUM

Database Object Permissions Lost

Database Object Permissions Lost

SCRIPT

Stored procedure to script out user permissions

Stored procedure to script out user permissions

SCRIPT

SQL 2005 - List Permission

This script will list user permissions for database objects.

SCRIPT

Retrieve Database Object permission scripts and Role members scripts

I often need to synchronize the user permission between databases. I wrote this simple query to scri...

Tags
security    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones