Generate Script for Database Permissions

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

A more lightweight scripting of the database security. Concentrates on inside the DB and not a full server integration like the migration script.

Code

SELECT CASE dperms.state_descWHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'ELSE state_descEND+ ' ' + permission_name + ' ON ' +CASE dperms.classWHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'WHEN 1 THENCASE dperms.minor_idWHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'ENDWHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(major_id) + ']'WHEN 4 THEN 'USER::[' + USER_NAME(major_id) + ']'WHEN 24 THEN 'SYMMETRIC KEY::[' + symm.[name] + ']'WHEN 25 THEN 'CERTIFICATE::[' + certs.[name] + ']'WHEN 26 THEN 'ASYMMETRIC KEY::[' + asymm.[name] +']'END+ ' TO [' + dprins.[name] + ']' +CASE dperms.state_descWHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'ELSE ';'END COLLATE database_default AS 'Permissions'FROM sys.database_permissions dpermsINNER JOIN sys.database_principals dprinsON dperms.grantee_principal_id = dprins.principal_idLEFT JOIN sys.columns colON dperms.major_id = col.object_id AND dperms.minor_id = col.column_idLEFT JOIN sys.objects objON dperms.major_id = obj.object_idLEFT JOIN sys.schemas schON obj.schema_id = sch.schema_idLEFT JOIN sys.asymmetric_keys asymmON dperms.major_id = asymm.asymmetric_key_idLEFT JOIN sys.symmetric_keys symmON dperms.major_id = symm.symmetric_key_idLEFT JOIN sys.certificates certsON dperms.major_id = certs.certificate_idWHERE dperms.type <> 'CO'AND dperms.major_id > 0;