how to know what are the current schemas are there in synapse

Rohit Boddu 461 Reputation points
2021-02-09T12:46:11.367+00:00

how to know what are the current schemas are there in synapse and who are the users acceessing it with their access like read and write

Azure SQL Database
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,457 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nandan Hegde 29,901 Reputation points MVP
    2021-02-09T16:05:25.617+00:00

    Hey @Rohit Boddu ,
    You can build on top of the below 2 commands:

    SELECT r.name role_principal_name, m.name AS member_principal_name
    FROM sys.database_role_members rm
    JOIN sys.database_principals r
    ON rm.role_principal_id = r.principal_id
    JOIN sys.database_principals m
    ON rm.member_principal_id = m.principal_id

    --List database roles & members
    SELECT DP1.name AS DatabaseRoleName,
    isnull (DP2.name, 'No members') AS DatabaseUserName
    FROM sys.database_role_members AS DRM
    RIGHT OUTER JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
    LEFT OUTER JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
    WHERE DP1.type IN ( 'R')
    ORDER BY DP1.name;

    0 comments No comments

0 additional answers

Sort by: Most helpful