Thursday, February 16, 2017

Creating multiple administrative accounts in Azure SQL Database Server

You know that we can create multiple administrator accounts in SQL Server if it is a on-premises instance or an instance configured in a VM (hosted in cloud or on-premises). What we generally do is, create a login and add the login to sysadmin fixed server role. Can we do the same in Azure SQL Database (Server)?

There is only one unrestricted administrative account that can be be created in Azure SQL Database Server. It is created when creating the server. In addition to that, you can add either one Azure Active Directory Account or Security Group Account (that has multiple accounts as members). If you open the Properties of your server, you can see your administrative accounts;


However, if you need to add multiple administrative accounts (not unrestricted administrative accounts), then there is a way of adding them. There are two server-level administrative roles that are available in the master database which user accounts can be added for granting permissions for creating and managing databases and logins.

The first role is dbmanager. This role has permission to create new databases, hence members of this role can create databases. This role exist in the master database. Therefore, only users in the master database can become members of this role.

-- In master database
-- creating a user
CREATE USER Dinesh2 WITH PASSWORD = 'Pa$$w0rd';

-- adding the user to dbmanager role
ALTER ROLE dbmanager ADD MEMBER Dinesh2;

The other role is loginmanager. This role has permissions for creating logins in the master database. Again, this role is master database, hence only users in master database can become members of it.

-- In master database
-- creating a user
CREATE USER Dinesh3 WITH PASSWORD = 'Pa$$w0rd';

-- adding the user to loginmanager role
ALTER ROLE loginmanager ADD MEMBER Dinesh2;

No comments:

Post a Comment