How to give the loginmanager role to a server login on Azure SQL?-CodePudding

How can I give the loginmanager server role to a login account on Azure SQL?

The role is

Any ideas here? I'm trying to create a second server login with this role so that I can avoid using the server admin account for everything.

CodePudding user response：

  -- add contained Azure AD user 
    CREATE USER [[email protected]] 
    FROM EXTERNAL PROVIDER 
    WITH DEFAULT_SCHEMA = dbo;  
      
    -- add user to role(s) in db 
    ALTER ROLE db_datareader ADD MEMBER [test]; 

    ALTER ROLE db_datawriter ADD MEMBER [test]; 
-- add login  
CREATE LOGIN [test2] 
WITH PASSWORD='SuperSecret!';  
  
-- add user 
CREATE USER [test2] 
FROM LOGIN [test2] 
WITH DEFAULT_SCHEMA=dbo; 
  
-- add user to role(s) in db 
ALTER ROLE dbmanager ADD MEMBER [test2]; 
ALTER ROLE loginmanager ADD MEMBER [test2];

CodePudding user response：

Connect to your Azure SQL Database server with SSMS as an admin in master. Create a SQL authentication login called ‘test’ with a password of ‘SuperSecret!’, create a user mapped to the login called ‘test’ in a database, and then add it to the db_datareader and db_datawriter roles

Open another query window and choose your user database in the dropdown -- select your db in the dropdown and create a user mapped to a login

-- add user to role(s) in db

Repeat second step for all databases you are adding ‘test’ to. Note, you will need to open a new connection(s). Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add the user(s) to in the dropdown. Create a SQL authentication contained user called ‘test’ with a password of ‘SuperSecret!’ then adding it to the db_datareader and db_datawriter roles.

Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add a user to in the dropdown. Add Azure Active Directory user ‘[email protected]’ then add it to the db_datareader and db_datawriter roles.

Connect to your Azure SQL Database server with SSMS as an admin in master. Add a SQL authentication login called ‘test2’ and a password of ‘SuperSecret!’, create a user mapped to it in master and add the user to the dbmanager and login manager roles.

Lastly, we'll add an Azure Active Directory user that can also manage databases and logins. Connect to your Azure SQL Database server with SSMS as an admin in master. Add a contained user ‘[email protected]’ and add it to the dbmanager and login manager roles.