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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,148 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: