January 24, 2022 at 8:30 am
This Question is for an Azure PaaS Database.
Does anybody has a Good Script to find out the List of all the Users who have access to a Particular Schema of a Database; if we can even show READ/WRITE/EXECUTE that would be Great.
We need only on Schema Level.
Many Thanks for your Help.
January 24, 2022 at 2:46 pm
Pretty sure this query will do everything you're asking for and more. It's from DBA.StackExchange. I tested it in Azure. It worked. Another one from over didn't work (too many connections to logins, which you don't have in Azure). With some modification, I got it to work too:
SELECT CASE princ.[type]
WHEN 'S' THEN
princ.[name]
END AS [UserName],
CASE princ.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
princ.[name] AS [DatabaseUserName],
NULL AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc],
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--database user
sys.database_principals AS princ
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
WHERE princ.[type] IN ( 'S', 'U' )
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT CASE memberprinc.[type]
WHEN 'S' THEN
memberprinc.[name]
END AS [UserName],
CASE memberprinc.[type]
WHEN 'S' THEN
'SQL User'
WHEN 'U' THEN
'Windows User'
END AS [UserType],
memberprinc.[name] AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc],
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Role/member associations
sys.database_role_members AS members
JOIN
--Roles
sys.database_principals AS roleprinc
ON roleprinc.[principal_id] = members.role_principal_id
JOIN
--Role members (database users)
sys.database_principals AS memberprinc
ON memberprinc.[principal_id] = members.member_principal_id
LEFT JOIN
--Permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
LEFT JOIN sys.objects AS obj
ON perm.major_id = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT '{All Users}' AS [UserName],
'{All Users}' AS [UserType],
'{All Users}' AS [DatabaseUserName],
roleprinc.[name] AS [Role],
perm.permission_name AS [PermissionType],
perm.state_desc AS [PermissionState],
obj.type_desc AS [ObjectType], --perm.[class_desc],
OBJECT_NAME(perm.major_id) AS [ObjectName],
col.[name] AS [ColumnName]
FROM
--Roles
sys.database_principals AS roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions AS perm
ON perm.grantee_principal_id = roleprinc.[principal_id]
LEFT JOIN
--Table columns
sys.columns AS col
ON col.[object_id] = perm.major_id
AND col.column_id = perm.minor_id
JOIN
--All objects
sys.objects AS obj
ON obj.[object_id] = perm.major_id
WHERE
--Only roles
roleprinc.[type] = 'R'
AND
--Only public role
roleprinc.[name] = 'public'
AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY princ.[name],
OBJECT_NAME(perm.major_id),
col.[name],
perm.permission_name,
perm.state_desc,
obj.type_desc; --perm.[class_desc]
One of those ought to get you what you need.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply