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)

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.

 

NOTE:
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: 6586 | Views in the last 30 days: 23
 
Related Articles
FORUM

Sql Script to change the Login Permission

Sql Script to change the Login Permission

FORUM

Script to list all logins and permissions

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

SCRIPT

Report SQL SERVER 2005 Permissions

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

FORUM

scripting a database without db owner permissions

scripting a database without db owner permissions

SCRIPT

Permission scripting over all databases

Scripts permissions/roles for db user across all databases.

Tags
administration    
permissions    
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