So I recently had a need to script out all of the database permissions for a particular database in SQL Server.  So I just threw together a script that will do this for me.

I thought I would share.  Here you go:

declare @user varchar(128);
set @user = ''; -- leave blank to get permissions for all users;

select
case a.[state]
when 'D' then 'DENY'
when 'R' then 'REVOKE'
when 'G' then 'GRANT'
when 'W' then 'GRANT'
end + ' ' +
case a.[type]
when 'AL' then 'ALTER'
when 'ALAK' then 'ALTER ANY ASYMMETRIC KEY'
when 'ALAR' then 'ALTER ANY APPLICATION ROLE'
when 'ALAS' then 'ALTER ANY ASSEMBLY'
when 'ALCF' then 'ALTER ANY CERTIFICATE'
when 'ALDS' then 'ALTER ANY DATASPACE'
when 'ALED' then 'ALTER ANY DATABASE EVENT NOTIFICATION'
when 'ALFT' then 'ALTER ANY FULLTEXT CATALOG'
when 'ALMT' then 'ALTER ANY MESSAGE TYPE'
when 'ALRL' then 'ALTER ANY ROLE'
when 'ALRT' then 'ALTER ANY ROUTE'
when 'ALSB' then 'ALTER ANY REMOTE SERVICE BINDING'
when 'ALSC' then 'ALTER ANY CONTRACT'
when 'ALSK' then 'ALTER ANY SYMMETRIC KEY'
when 'ALSM' then 'ALTER ANY SCHEMA'
when 'ALSV' then 'ALTER ANY SERVICE'
when 'ALTG' then 'ALTER ANY DATABASE DDL TRIGGER'
when 'ALUS' then 'ALTER ANY USER'
when 'AUTH' then 'AUTHENTICATE'
when 'BADB' then 'BACKUP DATABASE'
when 'BALO' then 'BACKUP LOG'
when 'CL' then 'CONTROL'
when 'CO' then 'CONNECT'
when 'CORP' then 'CONNECT REPLICATION'
when 'CP' then 'CHECKPOINT'
when 'CRAG' then 'CREATE AGGREGATE'
when 'CRAK' then 'CREATE ASYMMETRIC KEY'
when 'CRAS' then 'CREATE ASSEMBLY'
when 'CRCF' then 'CREATE CERTIFICATE'
when 'CRDB' then 'CREATE DATABASE'
when 'CRDF' then 'CREATE DEFAULT'
when 'CRED' then 'CREATE DATABASE DDL EVENT NOTIFICATION'
when 'CRFN' then 'CREATE FUNCTION'
when 'CRFT' then 'CREATE FULLTEXT CATALOG'
when 'CRMT' then 'CREATE MESSAGE TYPE'
when 'CRPR' then 'CREATE PROCEDURE'
when 'CRQU' then 'CREATE QUEUE'
when 'CRRL' then 'CREATE ROLE'
when 'CRRT' then 'CREATE ROUTE'
when 'CRRU' then 'CREATE RULE'
when 'CRSB' then 'CREATE REMOTE SERVICE BINDING'
when 'CRSC' then 'CREATE CONTRACT'
when 'CRSK' then 'CREATE SYMMETRIC KEY'
when 'CRSM' then 'CREATE SCHEMA'
when 'CRSN' then 'CREATE SYNONYM'
when 'CRSO' then 'CREATE SEQUENCE'
when '' then 'Applies to: SQL Server 2012 through SQL Server 2014.'
when 'CRSV' then 'CREATE SERVICE'
when 'CRTB' then 'CREATE TABLE'
when 'CRTY' then 'CREATE TYPE'
when 'CRVW' then 'CREATE VIEW'
when 'CRXS' then 'CREATE XML SCHEMA COLLECTION'
when 'DL' then 'DELETE'
when 'EX' then 'EXECUTE'
when 'IM' then 'IMPERSONATE'
when 'IN' then 'INSERT'
when 'RC' then 'RECEIVE'
when 'RF' then 'REFERENCES'
when 'SL' then 'SELECT'
when 'SN' then 'SEND'
when 'SPLN' then 'SHOWPLAN'
when 'SUQN' then 'SUBSCRIBE QUERY NOTIFICATIONS'
when 'TO' then 'TAKE OWNERSHIP'
when 'UP' then 'UPDATE'
when 'VW' then 'VIEW DEFINITION'
when 'VWCT' then 'VIEW CHANGE TRACKING'
when 'VWDS' then 'VIEW DATABASE STATE'
end +
CASE a.[class]
WHEN 0 then '' --'DATABASE'
WHEN 1 then ' on object::' + case when schema_name(d.[schema_id]) is null then ''
else '[' + schema_name(d.[schema_id]) + '].'
end + '[' + object_name(a.major_id) + ']' +
case when a.minor_id <> 0 then ' (' + rtrim(g.name) + ')'
else '' end -- 'OBJECT_OR_COLUMN'
WHEN 3 then ' on schema::[' + schema_name(a.[major_id]) + ']' --'SCHEMA'
WHEN 4 then ' on user::[' + rtrim(c.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'DATABASE_PRINCIPAL'
WHEN 5 then ' on assembly::[' + rtrim(h.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'ASSEMBLY'
WHEN 6 then ' on type::[' + schema_name(i.[schema_id]) + '].[' + rtrim(i.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'TYPE'
WHEN 10 then ' on xml schema collection::[' + rtrim(j.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'XML_SCHEMA_COLLECTION'
WHEN 15 then ' on message type::[' + rtrim(s.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'MESSAGE_TYPE'
WHEN 16 then ' on service contract::[' + rtrim(p.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'SERVICE_CONTRACT'
WHEN 17 then ' on service::[' + rtrim(q.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'SERVICE'
WHEN 18 then ' on remote service binding::[' + rtrim(r.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'REMOTE_SERVICE_BINDING'
WHEN 19 then ' on route::[' + rtrim(n.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'ROUTE'
WHEN 23 then ' on full text catalog::[' + rtrim(o.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'FULL_TEXT_CATALOG'
WHEN 24 then ' on symmetric key::[' + rtrim(m.name) collate SQL_Latin1_General_CP1_CI_AS + ']' -- 'SYMMETRIC_KEYS'
WHEN 25 then ' on certificate::[' + rtrim(k.name) collate SQL_Latin1_General_CP1_CI_AS + ']' --'CERTIFICATE'
WHEN 26 then ' on asymmetric key::[' + rtrim(l.name) collate SQL_Latin1_General_CP1_CI_AS + ']' -- 'ASYMMETRIC_KEY'
else ''
end +
' to [' + rtrim(b.name) collate SQL_Latin1_General_CP1_CI_AS + ']' +
case a.[state]
when 'W' then ' WITH GRANT OPTION;'
else ';'
end
from sys.database_permissions as [a]
join sys.database_principals as [b] on b.principal_id = a.grantee_principal_id and (isnull(@user, '') = '' or b.name = @user)
left outer join sys.database_principals as [c] on c.principal_id = a.major_id
left outer join sys.all_objects as [d] on d.[object_id] = a.major_id
left outer join sys.all_objects as [e] on e.[object_id] = a.minor_id
left outer join sys.symmetric_keys as [f] on f.symmetric_key_id = a.major_id
left outer join sys.columns as [g] on g.[object_id] = a.major_id and g.column_id = a.minor_id
left outer join sys.assemblies as [h] on h.assembly_id = a.major_id
left outer join sys.types as [i] on i.user_type_id = a.major_id
left outer join sys.xml_schema_collections as [j] on j.xml_collection_id = a.major_id
left outer join sys.certificates as [k] on k.certificate_id = a.major_id
left outer join sys.asymmetric_keys as [l] on l.asymmetric_key_id = a.major_id
left outer join sys.symmetric_keys as [m] on m.symmetric_key_id = a.major_id
left outer join sys.routes as [n] on n.route_id = a.major_id
left outer join sys.fulltext_catalogs as [o] on o.fulltext_catalog_id = a.major_id
left outer join sys.service_contracts as [p] on p.service_contract_id = a.major_id
left outer join sys.services as [q] on q.service_id = a.major_id
left outer join sys.remote_service_bindings as [r] on r.remote_service_binding_id = a.major_id
left outer join sys.service_message_types as [s] on s.message_type_id = a.major_id

If anyone has a better method of scripting out database permissions I would love to hear it.

Thanks!