add login to db_owner on master database in Azure SQL?

Question

Can we add login to db_owner role on master database in Azure SQL Single Database?

Answer 1

Hi,

Can we add login to db_owner role on master database in Azure SQL Single Database?

You can add USER as this is a database level role (LOGIN is an instance level entity)

Here is a full example or creating new LOGIN in the master -> creating new USER in the database -> adding the USER to the role

--> Connec to the master  
  
CREATE DATABASE [DB01]  (EDITION = 'Basic', SERVICE_OBJECTIVE = 'Basic', MAXSIZE = 2 GB)   
	WITH CATALOG_COLLATION = SQL_Latin1_General_CP1_CI_AS, LEDGER = OFF;  
GO  
  
IF not EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'Test_Login')  
    CREATE LOGIN [Test_Login] WITH PASSWORD=N'Not@A#Real$Password'  
GO  
  
-->> Connect the new database DB01  
  
if (Not Exists(select * from sys.sysusers where name = 'Test_User'))  
    CREATE USER [Test_User] FOR LOGIN [Test_Login] WITH DEFAULT_SCHEMA=[dbo]  
GO  
  
-- Add user to role (option 1)  
IF ((select ISNULL(is_rolemember('db_owner', 'Test_User'),0)) <> 1)  
    ALTER ROLE [db_owner] ADD MEMBER [Test_User];  
GO  
  
-- Add user to role (option 2)  
IF ((select ISNULL(is_rolemember('db_owner', 'Test_User'),0)) <> 1)  
    EXEC sp_addrolemember 'db_owner', 'Test_User'  
GO  
  
-- Get all members in the role 'db_owner'  
SELECT DatabaseRoleName = DP1.name, DatabaseUserName = DP2.name, DatabaseRoleName = DP1.type_desc  
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.name = 'db_owner'  
GO