CODEWITHSUNDEEP
azureazure-sql-database
GalmWing
GalmWing

Reputation: 751

how to know all dbo_owner users in an Azure SQL DB, including those mapped to 'dbo'?

The important part of my question is the "including those mapped to 'dbo'"

I need to check if a particular user has the db_owner role on an Azure SQL database. I am able to get the roles for users which have explicitly been granted db_owner access, but not for the Server admin or Azure Active Directory admin (which were created through the Azure Portal), because they map to the user 'dbo', but I'm not able to know which users are dbo.

To solve my issue I either need a full list of all users without the dbo mapping or a way to know who is mapped to dbo. Is there a way I can get any of those?

Upvotes: 1

Views: 9020

Answers (1)

axfd
axfd

Reputation: 321

As far as I known, the server admin and Active Directory admin enter the user databases as the dbo user. Meanwhile, the owner of a user database also enters the database as the dbo user.But the server admin and Active Directory admin do not enter the master database as the dbo user. For more details, please refer to official document. Besides, if you want to get all db_owner role user, please try the script.

 select @@ServerName [Server Name], DB_NAME() [DB Name], u.name [DB Role], u2.name [Member Name]
    from sys.database_role_members m
    join sys.database_principals u on m.role_principal_id = u.principal_id
    join sys.database_principals u2 on m.member_principal_id = u2.principal_id
    where u.name = 'db_owner'
    order by [Member Name]

Beside if you want to know who login the user database as dbo, please try the code.

#in user database run the command 
SELECT name, sid FROM sys.sysusers where name = 'dbo' . 
#in master database run the command 
SELECT name, sid FROM sys.sql_logins

Upvotes: 5

Related Questions