:::: MENU ::::

Generate script for all permissions in a database

  • Mar 06 / 2009
  • 3
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Generate script for all permissions in a database

In many scenarios DBA is required to configure permissions of a user. If permissions are to be re configured for an existing user, due to user deletion or some other reason, then it may be a nightmare to exactly configure the same. A handy solution is that you may generate the permissions for your users regularly in a usable form and use it when ever required. Following script will serve the same purpose. T-SQL for Permissions may be generated by following script. This T-SQL may be used later with a single click whenever required. The script will be used on SQL Server 2005 and later

 
Use AdventureWorks  
 GO  
 SELECT  
 state_desc + ' ' + permission_name +  
 ' on ['+ SYS.SCHEMAS.name + '].[' + SYS.OBJECTS.name + ']  
 to [' + SYS.DATABASE_PRINCIPALS.name + ']'  
 COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]  
 FROM SYS.DATABASE_PERMISSIONS JOIN sys.objects  
 ON SYS.DATABASE_PERMISSIONS.major_id = sys.objects.OBJECT_ID  
 JOIN SYS.SCHEMAS  
 ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID  
 JOIN SYS.DATABASE_PRINCIPALS  
 ON SYS.DATABASE_PERMISSIONS.grantee_principal_id = SYS.DATABASE_PRINCIPALS.principal_id  
 order by [Permissions T-SQL]  
 GO  

each and every permission will be provided in T-SQL form for every user. Whole result may be saved to use later.

T-SQL for permissions

If you require to generate permissions for a single user or single object then conditions may be applied through where clause in our script.

  • For specific object put [SYS.SCHEMAS.name] in where clause
  • For specific user put [SYS.DATABASE_PRINCIPALS.name] in where clause

To get all permissions for a login in all databases please read my other post.

  • Thanks for posting this. I was hoping I wouldn't have to re-invent the wheel for this.

  • This is Excellent – I would like to know how to loop through all of the db's in my server (80+) and capture permissions for one user and so i could use as a script for another….

  • Atif Shehzad

    Thanks for feedback by both. I have tried to accomplish the task said by ca_dev. Please visit my new post to get the script and description.
    This script would generate permissions for a specific login in all databases.

Consult us to explore the Databases. Contact us