CODEWITHSUNDEEP
sqlazure-sql-databasessms
dlimes
dlimes

Reputation: 105

Query all Database Users in Azure SQL Database, as well as any Role's they possess

If you navigate in SSMS through the database tree view, I have created users in DB>Security>Users that I am attempting to query all user within my Azure SQL Database, but am having difficult doing so.

I am not only trying to query all the users in this area, but am attempting to query what roles they have been assigned as well. I have been Googling for a while, and can't seem to find anything that I'm looking for. Please let me know if you can help with this, or if this is even possible.

Upvotes: 2

Views: 9645

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

The query below will list users and their database role memberships.

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE
    u.type NOT IN('R', 'G')
ORDER BY
      UserName
    , RoleName;

Upvotes: 8

Programnik
Programnik

Reputation: 1555

select * from master.sys.server_principals

Upvotes: 0

Related Questions