Monitoring and auditing the roles and members for those roles in your Azure Synapse Analytics SQL Pool is critical for security reasons. If you are looking to list the roles and users (members) in Azure Synapse Analytics SQL Pool, you can find the query that will help you get that information in this post.
The query below will help you list all the database roles and users in your Azure Synapse Analytics SQL Pool.
-- This query returns the roles and users
-- use this query to audit membership of the database roles
SELECT
r.[name] AS [Role]
, m.[name] AS [Member]
, m.Create_date AS [Created Date]
, m.modify_Date AS [Modified Date]
FROM
sys.database_role_members rm
JOIN sys.database_principals AS r ON rm.[role_principal_id] = r.[principal_id]
JOIN sys.database_principals AS m ON rm.[member_principal_id] = m.[principal_id]
WHERE
r.[type_desc] = 'DATABASE_ROLE';
WHAT’S NEXT?
In upcoming blog posts, we’ll continue to explore some of the features within Azure Services.
Please follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!
As always, please leave any comments or questions below.
