/* Script out Database permissions */
SET NOCOUNT ON
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) + ' /* !!! IMPORTANT VERIFY THE
SERVERNAME AND DATABASE NAME BEFORE EXECUTING THE SCRIPT !!! */'
/*SCRIPT OUT DATABASE USERS*/
SELECT 'CREATE USER ['+ [Link] collate database_default +'] FOR LOGIN [' +
[Link]+']'
AS '/*DB USERS*/'
FROM SYS.DATABASE_PRINCIPALS DP
JOIN SYS.SERVER_PRINCIPALS SP
ON [Link] =[Link] AND DP.PRINCIPAL_ID > 4
/* SCRIPT DATABASE LEVEL ROLE MEMBERS */
SELECT 'ALTER ROLE ['+USER_NAME(RM.ROLE_PRINCIPAL_ID) +'] ADD MEMBER [' +
USER_NAME(RM.MEMBER_PRINCIPAL_ID) +']'
AS '/*DB ROLE MEMBERS*/'
FROM SYS.DATABASE_ROLE_MEMBERS RM
JOIN SYS.DATABASE_PRINCIPALS DP
ON RM.MEMBER_PRINCIPAL_ID =DP.PRINCIPAL_ID AND RM.MEMBER_PRINCIPAL_ID > 4
JOIN SYS.SERVER_PRINCIPALS SP
ON [Link] =[Link]
/* SCRIPT DB LEVEL PERMISSIONS */
SELECT
STATE_DESC+' '+ DM.PERMISSION_NAME+ ' TO ['+USER_NAME(DM.GRANTEE_PRINCIPAL_ID)+']'+
CASE [Link]
WHEN 'W' THEN ' WITH GRANT OPTION'
ELSE ''
END
AS '/*DB LEVEL PERMISSIONS*/'
FROM SYS.DATABASE_PERMISSIONS DM
JOIN SYS.DATABASE_PRINCIPALS DP
ON DM.GRANTEE_PRINCIPAL_ID =DP.PRINCIPAL_ID AND DM.GRANTEE_PRINCIPAL_ID >4 AND
[Link]=0
JOIN SYS.SERVER_PRINCIPALS SP
ON [Link]=[Link]
/* SCRIPT DB OBJECT LEVEL PERMISSIONS */
SELECT
CASE [Link]
WHEN 'W' THEN 'GRANT'
ELSE DM.STATE_DESC
END
+' '+
CASE DM.PERMISSION_NAME
WHEN 'REFERENCES' THEN CASE DM.MINOR_ID
WHEN 0 THEN DM.PERMISSION_NAME
ELSE
DM.PERMISSION_NAME+'('+COL_NAME(DM.MAJOR_ID,DM.MINOR_ID)+')'
END
ELSE DM.PERMISSION_NAME
END
+' ON OBJECT::[' + OBJECT_SCHEMA_NAME(DM.MAJOR_ID)+'].['+OBJECT_NAME(DM.MAJOR_ID)
+']'+
' TO ['+USER_NAME(DM.GRANTEE_PRINCIPAL_ID)+']'+
CASE [Link]
WHEN 'W' THEN ' WITH GRANT OPTION'
ELSE ''
END
AS '/*DB OBJECT LEVEL PERMISSIONS*/'
FROM SYS.DATABASE_PERMISSIONS DM
JOIN SYS.DATABASE_PRINCIPALS DP
ON DM.GRANTEE_PRINCIPAL_ID =DP.PRINCIPAL_ID AND DM.GRANTEE_PRINCIPAL_ID >4 AND
[Link]=1
JOIN SYS.SERVER_PRINCIPALS SP
ON [Link]=[Link]
/* SCRIPT DB SCHEMA LEVEL PERMISSIONS */
SELECT
CASE [Link]
WHEN 'W' THEN 'GRANT '
ELSE DM.STATE_DESC
END
+' '+
DM.PERMISSION_NAME
+' ON SCHEMA::[' + SCHEMA_NAME(DM.MAJOR_ID)+']'+
' TO ['+USER_NAME(DM.GRANTEE_PRINCIPAL_ID)+']'+
CASE [Link]
WHEN 'W' THEN ' WITH GRANT OPTION'
ELSE ''
END
AS '/*DB SCHEMA LEVEL PERMISSIONS*/'
FROM SYS.DATABASE_PERMISSIONS DM
JOIN SYS.DATABASE_PRINCIPALS DP
ON DM.GRANTEE_PRINCIPAL_ID =DP.PRINCIPAL_ID AND DM.GRANTEE_PRINCIPAL_ID >4 AND
[Link]=3
JOIN SYS.SERVER_PRINCIPALS SP
ON [Link]=[Link]
/*ANY OTHER PERMISSIONS*/
SELECT
CASE [Link]
WHEN 'W' THEN 'GRANT'
ELSE DM.STATE_DESC
END
+' '+
DM.PERMISSION_NAME
+' '+
CASE [Link]
WHEN 4 THEN 'ON ' + (SELECT RIGHT(TYPE_DESC, 4) + '::[' + NAME FROM
SYS.DATABASE_PRINCIPALS WHERE PRINCIPAL_ID = DM.MAJOR_ID) + '] '
WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT NAME FROM [Link] WHERE
ASSEMBLY_ID = DM.MAJOR_ID) + '] '
WHEN 6 THEN 'ON TYPE::[' + (SELECT NAME FROM [Link] WHERE USER_TYPE_ID =
DM.MAJOR_ID) + '] '
WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(SCHEMA_ID) +
'.' + NAME FROM SYS.XML_SCHEMA_COLLECTIONS WHERE XML_COLLECTION_ID = DM.MAJOR_ID) +
'] '
WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT NAME FROM
SYS.SERVICE_MESSAGE_TYPES WHERE MESSAGE_TYPE_ID = DM.MAJOR_ID) + '] '
WHEN 16 THEN 'ON CONTRACT::[' + (SELECT NAME FROM SYS.SERVICE_CONTRACTS WHERE
SERVICE_CONTRACT_ID = DM.MAJOR_ID) + '] '
WHEN 17 THEN 'ON SERVICE::[' + (SELECT NAME FROM [Link] WHERE SERVICE_ID
= DM.MAJOR_ID) + '] '
WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT NAME FROM
SYS.REMOTE_SERVICE_BINDINGS WHERE REMOTE_SERVICE_BINDING_ID = DM.MAJOR_ID) + '] '
WHEN 19 THEN 'ON ROUTE::[' + (SELECT NAME FROM [Link] WHERE ROUTE_ID =
DM.MAJOR_ID) + '] '
WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT NAME FROM SYS.FULLTEXT_CATALOGS
WHERE FULLTEXT_CATALOG_ID = DM.MAJOR_ID) + '] '
WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT NAME FROM SYS.SYMMETRIC_KEYS WHERE
SYMMETRIC_KEY_ID = DM.MAJOR_ID) + '] '
WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT NAME FROM [Link] WHERE
CERTIFICATE_ID = DM.MAJOR_ID) + '] '
WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT NAME FROM SYS.ASYMMETRIC_KEYS
WHERE ASYMMETRIC_KEY_ID = DM.MAJOR_ID) + ']'
END COLLATE DATABASE_DEFAULT
+
' TO ['+USER_NAME(DM.GRANTEE_PRINCIPAL_ID)+']'+
CASE [Link]
WHEN 'W' THEN ' WITH GRANT OPTION'
ELSE ''
END
AS '/*OTHER PERMISSIONS*/'
FROM SYS.DATABASE_PERMISSIONS DM
JOIN SYS.DATABASE_PRINCIPALS DP
ON DM.GRANTEE_PRINCIPAL_ID =DP.PRINCIPAL_ID AND DM.GRANTEE_PRINCIPAL_ID >4 AND
[Link] >=4
JOIN SYS.SERVER_PRINCIPALS SP
ON [Link]=[Link]