-- ROLE MEMBERS
SELECT mp.name AS MemberName, rp.name AS RoleName, 'EXEC sp_addrolemember N''' + rp.name + ''', N''' + mp.name + '''' AS AssignSQL,
'EXEC sp_droprolemember N''' + rp.name + ''', N''' + mp.name + '''' AS RemoveSQL
FROM sys.database_role_members a
INNER JOIN sys.database_principals rp ON rp.principal_id = a.role_principal_id
INNER JOIN sys.database_principals AS mp ON mp.principal_id = a.member_principal_id
-- PERMISSIONS
SELECT d.class, d.class_desc, p.name AS UserName, s.name AS GrantedObject,
StateDesc + ' ' + PermissionName + ' ON SCHEMA::[' + s.name + '] TO [' + p.name + '];' AS ApplySQL,
'REVOKE ' + PermissionName + ' ON SCHEMA::[' + s.name + '] TO [' + p.name + '];' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.schemas AS s ON s.schema_id = d.major_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 3 /*'SCHEMA'*/
UNION ALL
SELECT d.class, d.class_desc, p.name AS UserName, DB_NAME() AS GrantedObject, StateDesc + ' ' + PermissionName + ' TO [' + p.name + '];' AS ApplySQL,
'REVOKE ' + PermissionName + ' TO [' + p.name + '];' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 0 /*DATABASE*/
UNION ALL
SELECT d.class, d.class_desc, p.name AS UserName, '[' + s.name + '].[' + o.name + ']' + ISNULL(' ([' + co.NAME + ']) ', '') AS GrantedObject,
StateDesc + ' ' + PermissionName + ' ON [' + s.name + '].[' + o.name + '] ' + ISNULL('([' + co.NAME + ']) ', '') + 'TO [' + p.name + '];' AS ApplySQL,
'REVOKE ' + PermissionName + ' ON [' + s.name + '].[' + o.name + '] ' + ISNULL('([' + co.NAME + ']) ', '') + 'TO [' + p.name + '];' AS RemoveSQL
FROM sys.database_permissions d
INNER JOIN sys.database_principals p ON d.grantee_principal_id = p.principal_id
INNER JOIN sys.objects AS o ON o.object_id = d.major_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.columns co ON co.object_id = o.object_id
AND co.column_id = d.minor_id
CROSS APPLY (SELECT state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName) ca
WHERE d.class = 1 /*OBJECT OR COLUMN*/
