Tuesday, October 4, 2016

What are dbmanager and loginmanager database roles in Azure SQL Database instance

Have you noticed two new database roles appeared in master database? You might have, you might have not, because these two appear only with Azure SQL Database instance. What are they and what is the usage of them?

dbmanager database role
Azure SQL Database instance does not offer any server level roles and no user can be created similar to sa that we have seen with on-premise instances. Since we do not have administrative control over Azure SQL Database instance, the dbmanager role is given to us inside the virtual master database. Generally, when the instance is created using the portal, the account added becomes the admin of the instance and account will be added to the master database. If you need more administrators who can create databases, then you need to create a new user and add the user to this role.

Here are the steps. You need to make sure that you are connected with master database.

CREATE USER Jane WITH PASSWORD = 'Pa$$w0rd'; 
ALTER ROLE dbmanager ADD MEMBER Jane; 

dbmanager database role
Just like the above one, if you need users who can perform operations related to users such as create, alter user account, create a user and add him to this role.

CREATE USER Jack WITH PASSWORD = 'Pa$$w0rd'; 
ALTER ROLE loginmanager ADD MEMBER Jack; 

However, this is not much required and important with Azure SQL Databases as almost all databases are treated as Contained Databases. Therefore required permissions can be simply granted to user as below without creating user accounts in master and adding them to loginmanager role.

GRANT ALTER ANY USER TO Jack;


No comments:

Post a Comment