;WITH rolemem
AS (SELECT DB_NAME() COLLATE SQL_Latin1_General_CP1_CI_AS AS DatabaseName,
mp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS MemberName,
rp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS RoleName
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)
-- ROLE MEMBERS
SELECT DatabaseName,
MemberName,
RoleName,
'EXEC sp_addrolemember N''' + RoleName + ''', N''' + MemberName + '''' AS AssignSQL,
'EXEC sp_droprolemember N''' + RoleName + ''', N''' + MemberName + '''' AS RemoveSQL
FROM rolemem
;WITH perms
AS (SELECT DB_NAME() COLLATE SQL_Latin1_General_CP1_CI_AS AS DatabaseName,
d.CLASS,
d.class_desc COLLATE SQL_Latin1_General_CP1_CI_AS class_desc,
p.name COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
s.name COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName,
o.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ObjectName,
co.name COLLATE SQL_Latin1_General_CP1_CI_AS AS ColumnName,
state_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS StateDesc,
permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS PermissionName,
p.name COLLATE SQL_Latin1_General_CP1_CI_AS AS PrincipalName
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 d.CLASS = 1
AND co.OBJECT_ID = o.OBJECT_ID
AND co.column_id = d.minor_id)
-- PERMISSIONS
SELECT DatabaseName,
CLASS,
class_desc,
UserName,
SchemaName AS GrantedObject,
StateDesc + ' ' + PermissionName + ' ON SCHEMA::[' + SchemaName + '] TO [' + PrincipalName + '];' AS ApplySQL,
'REVOKE ' + PermissionName + ' ON SCHEMA::[' + SchemaName + '] TO [' + PrincipalName + '];' AS RemoveSQL
FROM perms
WHERE CLASS = 3 /*'SCHEMA'*/
UNION ALL
SELECT DatabaseName,
CLASS,
class_desc,
PrincipalName AS UserName,
DB_NAME() AS GrantedObject,
StateDesc + ' ' + PermissionName + ' TO [' + PrincipalName + '];' AS ApplySQL,
'REVOKE ' + PermissionName + ' TO [' + PrincipalName + '];' AS RemoveSQL
FROM perms
WHERE CLASS = 0 /*DATABASE*/
UNION ALL
SELECT DatabaseName,
CLASS,
class_desc,
PrincipalName AS UserName,
'[' + SchemaName + '].[' + ObjectName + ']' + ISNULL(' ([' + ColumnName + ']) ', '') AS GrantedObject,
StateDesc + ' ' + PermissionName + ' ON [' + SchemaName + '].[' + ObjectName + '] ' + ISNULL('([' + ColumnName
+ ']) ', '')
+ 'TO [' + PrincipalName + '];' AS ApplySQL,
'REVOKE ' + PermissionName + ' ON [' + SchemaName + '].[' + ObjectName + '] ' + ISNULL('([' + ColumnName + ']) ',
'') + 'TO ['
+ PrincipalName + '];' AS RemoveSQL
FROM perms
WHERE CLASS = 1 /*OBJECT OR COLUMN*/