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.
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.