Forum Discussion
Azure SQL server get all users and their roles
Please try this:
List of Users:
SELECT name ,type_desc ,create_date ,CASE WHEN is_disabled = 1 THEN 'Disabled' ELSE 'Enabled' END AS is_disabled FROM sys.sql_logins ORDER BY name
List of Users by role:
SELECT
DP1.name AS DatabaseRoleName
,ISNULL(DP2.name, 'No members') AS DatabaseUserName
,DP2.principal_id
,DP2.create_date
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 = 'R'
ORDER BY DP1.name, ISNULL(DP2.name, 'No members');
This will return db_owner but I need to know who are ALL the db_owners.
I can get it to return my account showing I am a db_owner on the Database with this command:
IF IS_ROLEMEMBER ('db_owner') = 1
print 'Current user is a member of the db_owner role'
ELSE IF IS_ROLEMEMBER ('db_owner') = 0
print 'Current user is NOT a member of the db_owner role'
ELSE IF IS_ROLEMEMBER ('db_owner') IS NULL
print 'ERROR: The database role specified is not valid.';I opened a ticket with Microsoft. In Azure Portal go to the SQL Database and then click on Properties, in there we have Active Directory Admin. That is a security account that was created. We then go to Azure Active Directory and look for that Group and click on Members. Those are the DBO's for the Azure SQL Database. In addition, we have other DBO's that are external and those can be found by running the script listed by @Kidd_lp
SELECT
DP1.name AS DatabaseRoleName
,ISNULL(DP2.name, 'No members') AS DatabaseUserName
,DP2.principal_id
,DP2.create_date
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 = 'R'
ORDER BY DP1.name, ISNULL(DP2.name, 'No members');
