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)
Thank this author by sharing:
By Richard Lu,
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 = '%'
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 usersDatabase 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.
Sql Script to change the Login Permission
Need to identify any logins and their permissions to the production database
Reports permissions to three levels: database, schema and table -- in three formats: readable, struc...
scripting a database without db owner permissions
Scripts permissions/roles for db user across all databases.