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.
