sp_AzSYNDBPermissions.sql

/*********************************************************************************************
sp_AzSYNDBPermissions V1.0
Kenneth Fisher
 
http://www.sqlstudies.com
 
This stored procedure returns 3 data sets.  The first dataset is the list of database
principals, the second is role membership, and the third is object and database level
permissions.
    
The final 2 columns of each query are "Un-Do"/"Do" scripts.  For example removing a member
from a role or adding them to a role.  I am fairly confident in the role scripts, however, 
the scripts in the database principals query and database/object permissions query are 
works in progress.  In particular certificates, keys and column level permissions are not
scripted out.  Also while the scripts have worked flawlessly on the systems I've tested 
them on, these systems are fairly similar when it comes to security so I can't say that 
in a more complicated system there won't be the odd bug.
    
Standard disclaimer: You use scripts off of the web at your own risk.  I fully expect this
     script to work without issue but I've been known to be wrong before.

Data is ordered as follows
    1st result set: DBPrincipal
    2nd result set: RoleName, UserName if the parameter @Role is used else
                    UserName, RoleName
    3rd result set: ObjectName then Grantee_Name if the parameter @ObjectName
                    is used otherwise Grantee_Name, ObjectName

Because of complications when using Azure Synapse there are no parameters. This is
strictly all of the permissions in the database and all three outputs.

-- V1.0
-- 8/31/2020 – Create sp_AzSYNDBPermissions based on queries from sp_AzSQLDBPermissionss
*********************************************************************************************/
    
CREATE PROCEDURE dbo.sp_AzSYNDBPermissions
AS
-- Database Principals
SELECT DBPrincipals.principal_id AS DBPrincipalId, DBPrincipals.name AS DBPrincipal, DBPrincipals.type, 
       DBPrincipals.type_desc, DBPrincipals.default_schema_name, DBPrincipals.create_date, 
       DBPrincipals.modify_date, DBPrincipals.is_fixed_role, 
       Authorizations.name AS RoleAuthorization, DBPrincipals.sid,  
       CASE WHEN DBPrincipals.is_fixed_role = 0 AND DBPrincipals.name NOT IN ('dbo','guest', 'INFORMATION_SCHEMA', 'public', 'sys') THEN  
				'IF DATABASE_PRINCIPAL_ID(''' + DBPrincipals.name + ''') IS NOT NULL ' + 
               'DROP ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL 
                   WHEN 'K' THEN NULL 
                   WHEN 'R' THEN 'ROLE' 
                   WHEN 'A' THEN 'APPLICATION ROLE'  
                   ELSE 'USER' END + 
               ' '+QUOTENAME(DBPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS) + ';' ELSE NULL END AS DropScript, 
       CASE WHEN DBPrincipals.is_fixed_role = 0 AND DBPrincipals.name NOT IN ('dbo','guest', 'INFORMATION_SCHEMA', 'public', 'sys') THEN  
				'IF DATABASE_PRINCIPAL_ID(''' + DBPrincipals.name + ''') IS NULL ' + 
               'CREATE ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL 
                   WHEN 'K' THEN NULL 
                   WHEN 'R' THEN 'ROLE' 
                   WHEN 'A' THEN 'APPLICATION ROLE' 
                   ELSE 'USER' END + 
               ' '+QUOTENAME(DBPrincipals.name COLLATE SQL_Latin1_General_CP1_CI_AS) END +  
               CASE WHEN DBPrincipals.[type] = 'R' THEN 
                   ISNULL(' AUTHORIZATION '+QUOTENAME(Authorizations.name COLLATE SQL_Latin1_General_CP1_CI_AS),'')  
				   WHEN DBPrincipals.[type] = 'X' THEN ' FROM EXTERNAL PROVIDER'
                   WHEN DBPrincipals.[type] = 'A' THEN 
                       ''  
                   WHEN DBPrincipals.[type] NOT IN ('C','K') THEN 
                       ISNULL(' WITH DEFAULT_SCHEMA =  '+
                          QUOTENAME(DBPrincipals.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS),'') 
               ELSE '' END +
			   CASE WHEN DBPrincipals.[type] = 'S' 
					THEN ', PASSWORD = ''<Insert Strong Password Here>'' ' ELSE ''  END + 
               ';' 
           AS CreateScript 
    FROM sys.database_principals DBPrincipals 
    LEFT OUTER JOIN sys.database_principals Authorizations 
       ON DBPrincipals.owning_principal_id = Authorizations.principal_id 
    WHERE 1=1 
       AND DBPrincipals.sid NOT IN (0x00, 0x01) 
 
 
-- Database Role Members
SELECT Users.principal_id AS UserPrincipalId, Users.name AS UserName, Roles.name AS RoleName, 
   CASE WHEN Users.is_fixed_role = 0 AND Users.name <> 'dbo' THEN 
   'EXEC sp_droprolemember @rolename = '+QUOTENAME(Roles.name COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+', @membername = '+QUOTENAME(CASE WHEN Users.name = 'dbo' THEN NULL
            ELSE Users.name END COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+';' END AS DropScript, 
   CASE WHEN Users.is_fixed_role = 0 AND Users.name <> 'dbo' THEN 
   'EXEC sp_addrolemember @rolename = '+QUOTENAME(Roles.name COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+', @membername = '+QUOTENAME(CASE WHEN Users.name = 'dbo' THEN NULL
            ELSE Users.name END COLLATE SQL_Latin1_General_CP1_CI_AS,'''')+';' END AS AddScript 
FROM sys.database_role_members RoleMembers 
JOIN sys.database_principals Users 
   ON RoleMembers.member_principal_id = Users.principal_id 
JOIN sys.database_principals Roles 
   ON RoleMembers.role_principal_id = Roles.principal_id 
WHERE 1=1 
 
 
-- Database & object Permissions
; WITH ObjectList AS (
   SELECT SCHEMA_NAME(sys.all_objects.schema_id)  COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName,
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       object_id AS id, 
       'OBJECT_OR_COLUMN' AS class_desc,
       'OBJECT' AS class 
   FROM sys.all_objects
   UNION ALL
   SELECT name  COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName, 
       NULL AS name, 
       schema_id AS id, 
       'SCHEMA' AS class_desc,
       'SCHEMA' AS class 
   FROM sys.schemas
   UNION ALL
   SELECT NULL AS SchemaName, 
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       principal_id AS id, 
       'DATABASE_PRINCIPAL' AS class_desc,
       CASE type_desc 
           WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE' 
           WHEN 'DATABASE_ROLE' THEN 'ROLE' 
           ELSE 'USER' END AS class 
   FROM sys.database_principals
   UNION ALL
   SELECT NULL AS SchemaName, 
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       assembly_id AS id, 
       'ASSEMBLY' AS class_desc,
       'ASSEMBLY' AS class 
   FROM sys.assemblies
   UNION ALL
   SELECT SCHEMA_NAME(sys.types.schema_id)  COLLATE SQL_Latin1_General_CP1_CI_AS AS SchemaName, 
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       user_type_id AS id, 
       'TYPE' AS class_desc,
       'TYPE' AS class 
   FROM sys.types
   UNION ALL
   SELECT NULL AS SchemaName, 
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       symmetric_key_id AS id, 
       'SYMMETRIC_KEYS' AS class_desc,
       'SYMMETRIC KEY' AS class 
   FROM sys.symmetric_keys
   UNION ALL
   SELECT NULL AS SchemaName, 
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       certificate_id AS id, 
       'CERTIFICATE' AS class_desc,
       'CERTIFICATE' AS class 
   FROM sys.certificates
   UNION ALL
   SELECT NULL AS SchemaName, 
       name  COLLATE SQL_Latin1_General_CP1_CI_AS AS name, 
       asymmetric_key_id AS id, 
       'ASYMMETRIC_KEY' AS class_desc,
       'ASYMMETRIC KEY' AS class 
   FROM sys.asymmetric_keys 
   ) 

SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name AS GrantorName, 
   Permission.class_desc, Permission.permission_name, 
   ObjectList.name AS ObjectName, 
   ObjectList.SchemaName, 
   Permission.state_desc,  
   CASE WHEN Grantee.is_fixed_role = 0 AND Grantee.name <> 'dbo' THEN 
   'REVOKE ' + 
   CASE WHEN Permission.[state]  = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END + 
   ' ' + Permission.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS +  
       CASE WHEN Permission.major_id <> 0 THEN ' ON ' + 
           ObjectList.class + '::' +  
           ISNULL(QUOTENAME(ObjectList.SchemaName),'') + 
           CASE WHEN ObjectList.SchemaName + ObjectList.name IS NULL THEN '' ELSE '.' END + 
           ISNULL(QUOTENAME(ObjectList.name),'') 
            COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' ELSE '' END + 
       ' FROM ' + QUOTENAME(Grantee.name COLLATE SQL_Latin1_General_CP1_CI_AS)  + '; ' END AS RevokeScript, 
   CASE WHEN Grantee.is_fixed_role = 0 AND Grantee.name <> 'dbo' THEN 
   CASE WHEN Permission.[state]  = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS END +  
       ' ' + Permission.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + 
       CASE WHEN Permission.major_id <> 0 THEN ' ON ' + 
           ObjectList.class + '::' +  
           ISNULL(QUOTENAME(ObjectList.SchemaName),'') + 
           CASE WHEN ObjectList.SchemaName + ObjectList.name IS NULL THEN '' ELSE '.' END + 
           ISNULL(QUOTENAME(ObjectList.name),'') 
            COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' ELSE '' END + 
       ' TO ' + QUOTENAME(Grantee.name COLLATE SQL_Latin1_General_CP1_CI_AS)  + ' ' +  
       CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END +  
       ' AS '+ QUOTENAME(Grantor.name COLLATE SQL_Latin1_General_CP1_CI_AS)+';' END AS GrantScript 
FROM sys.database_permissions Permission 
JOIN sys.database_principals Grantee 
   ON Permission.grantee_principal_id = Grantee.principal_id 
JOIN sys.database_principals Grantor 
   ON Permission.grantor_principal_id = Grantor.principal_id 
LEFT OUTER JOIN ObjectList 
   ON Permission.major_id = ObjectList.id 
   AND Permission.class_desc = ObjectList.class_desc 
WHERE 1=1