sp_AzSYNDBPermissions

Leave a comment

	/*********************************************************************************************
	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