Azure SQL Pro Tip – Creating Login Account and User

With Azure Resource Manager (ARM), while creating an Azure SQL Database instance, we can only set up an admin account. As we all know, using this admin account is not safe in most cases. Therefore, we need to create another accounts with fewer privileges.

However, unlike MS SQL Server, Azure SQL Database has some restrictions. Those restrictions also apply to create login accounts and users. In this post, we are going to create login accounts with limited permissions on Azure SQL Database.

Creating Login Account

With ARM, once an admin account is ready, we need to connect to the SQL Database instance with the admin account, using its credentials.

Once you connect to the Azure SQL Database through SSMS, you will be able to see the screen like above. Make sure that you are now on the master database. Then run the following SQL query:

• <LOGIN_ACCOUNT> is the one you want to create.
• <ACCOUNT_PASSWORD> is the password of the account you want to create.

NOTE: We can’t use the DEFAULT_DATABASE option when creating a login account as we’re on Azure SQL Database. For more details, find this MSDN document.

The first query is to check if the login account already exists and, if exists, drop it. Of course you can skip this part. The second query is actually to create the login account.

If you are on a database other than master and run the SQL query above, you will get an error message like:

Make sure that you are on the master database. 🙂 We have created a new login account. Let’s move onto the next step.

Creating User on Database with Appropriate Permissions

In order to create a user and grant permissions on the user, in SSMS, we usually do like:

However, we are on Azure SQL Database. You will see the error message like:

According to the MSDN document, USE (Transact-SQL), we can’t use the USE statement on Azure SQL Database.

In Azure SQL Database, the database parameter can only refer to the current database. The USE statement does not switch between databases, error code 40508 is returned. To change databases, you must directly connect to the database.

Yes, we need to directly connect to the database. How can we do it?

As the admin account uses the master database as its default one, instead of using the <default> database, we should specify a particular database name like above. After directly connect to the database, run the following SQL query to create a user and give permissions to the user:

This script is to create a user and give a db_owner privilege. If you want more restricted one, try the following:

This only offers the user with SELECT, INSERT, UPDATE and DELETE permissions.

That’s it. So far, we have walked through how we could create a login account and a user of a database on Azure SQL Database. If we can create a login account with limited privileges, we can use Azure SQL Database with fewer worries.