Assign user to single database in Azure SQL Database

Security is vast topic in case of SQL Azure. In this post I will explain simple steps required to create user, assign db owner role to that user specific to single Azure SQL DB.

The advantage is, you don’t have to use admin credentials of Azure SQL DB server. Also the new use will not have any access to master database and hence accidental changes to other database are avoided.  

First login to Azure SQL management portal. Login to SQL Azure management portal using credentials of user which has access to master database. Such user can be server administrator for Azure SQL.

Then create a sample database. I will name it as “MyDB”. Now I need to create a user in such a way that it will have permission to perform any operation on MyDB only. So same user credentials can be used in applications connecting to SQL Azure.

Steps are as follows –

Create Login in master database query window.

Create Login kunal WITH PASSWORD = ‘yourPassword’ 

Create user in query window of MyDB database from above created login.

Create User kunalasuser FROM LOGIN kunal 

Then assign role as db_owner to the above created user in MyDB query window.

EXEC sp_addrolemember ‘db_owner’, ‘kunalasuser’ 

Now if you connect to your server from SQL server management studio then you will have select MyDB as database from options window of SSMs login. DB Ownerr role provides full permission to kunalasuser to perform any operation on database MyDB.

If you try to connect to master database then you will error as access denied. Hence we have mapped user to a single database in Azure SQL DB.

Similar to db_owner there are many different roles that can be assigned to any single user in Azure SQL database.

Hope this helps.

Cheers…

Happy Roleing!!!