SQL Clone
SQLServerCentral is supported by Redgate
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)

The Complete SQL Server 2005 Permissions Report

By Richard Lu,

Script Description:

The script provides detailed access permission report on all levels, i.e. server, database, object and column, of SQL Server 2005 for all logins. Users would also be able to customize the report result by specifying two parameters: @loginName and @dbName at the beginning of the script to retrieve permission assignments on particular logins and databases. Reports of permissions possessed by System fixed roles, e.g. public and SQLAgentOperatorRole, can be excluded from the report by simply removing the comment marks on all the "type NOT IN ('R')" condition in the Where-clause in the script.

How to Use:
Copy and paste the script to SQL Server 2005 Database Engine Query Window and run the query. By default, the query will report back with permissions on all Logins and databases.

To get a customized report on particular logins and/or databases, you will need to specify the @loginName and @dbname parameters. Below are examples of these parameters:

Example 1: Returns the complete permission report for all logins (Default setting)
SET @loginName = '%'
SET @dbName = '%'

Example 2: Returns the permission report for Windows logins 'NorthAmerica\JSmith1' in ReportServer DB.
SET @loginName = 'NorthAmerica\JSmith1'
SET @dbName = 'ReportServer'

Example 3: Returns the permission report for SQL logins Like 'reader%' in all databases.
SET @loginName = 'reader%'
SET @dbName = '%'

Result Sets:

The script provides three sets of results. One for SQL Server level, one for database level, and the last one for object\column level. Below is a screenshot of an example.


Orphan database users
Database users without a corresponding login in the database level permission matrix suggest that the users are orphan users. Exceptions go to some database roles, such as "guest", "public", and etc. It should be a best practise to address any orphan users with the database administrators.

Total article views: 6983 | Views in the last 30 days: 1
Related Articles

Sql Script to change the Login Permission

Sql Script to change the Login Permission


Script to list all logins and permissions

Need to identify any logins and their permissions to the production database


Report SQL SERVER 2005 Permissions

Reports permissions to three levels: database, schema and table -- in three formats: readable, struc...


scripting a database without db owner permissions

scripting a database without db owner permissions


Permission scripting over all databases

Scripts permissions/roles for db user across all databases.