I struggled a bit find to find which user is assigned to which resource class in Azure Synapse. After short effort I prepare this query to get the details. Thought it is worth sharing.
SELECT u.[name] as [User Name], p.[name] as [Resource Class] FROM sys.database_principals AS p
INNER JOIN sys.database_role_members AS r on p.principal_id = r.role_principal_id
INNER JOIN sys.sysusers AS u ON u.uid = r.member_principal_id
WHERE p.[name] like '%rc%' and u.[name] = 'user'
SELECT u.[name] as [User Name], p.[name] as [Resource Class] FROM sys.database_principals AS p
INNER JOIN sys.database_role_members AS r on p.principal_id = r.role_principal_id
INNER JOIN sys.sysusers AS u ON u.uid = r.member_principal_id
WHERE p.[name] like '%rc%' and u.[name] = 'user'
Happy Coding!!
This comment has been removed by a blog administrator.
ReplyDelete