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_desc

WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT'

ELSE state_desc

END

+ ' ' + permission_name + ' ON ' +

CASE dperms.class

WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']'

WHEN 1 THEN

CASE dperms.minor_id

WHEN 0 THEN 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + ']'

ELSE 'OBJECT::[' + sch.[name] + '].[' + obj.[name] + '] ([' + col.[name] + '])'

END

WHEN 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_desc

WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;'

ELSE ';'

END COLLATE database_default AS 'Permissions'

FROM sys.database_permissions dperms

INNER JOIN sys.database_principals dprins

ON dperms.grantee_principal_id = dprins.principal_id

LEFT JOIN sys.columns col

ON dperms.major_id = col.object_id AND dperms.minor_id = col.column_id

LEFT JOIN sys.objects obj

ON dperms.major_id = obj.object_id

LEFT JOIN sys.schemas sch

ON obj.schema_id = sch.schema_id

LEFT JOIN sys.asymmetric_keys asymm

ON dperms.major_id = asymm.asymmetric_key_id

LEFT JOIN sys.symmetric_keys symm

ON dperms.major_id = symm.symmetric_key_id

LEFT JOIN sys.certificates certs

ON dperms.major_id = certs.certificate_id

WHERE dperms.type <> 'CO'

AND dperms.major_id > 0;