How to Create User for Azure SQL Database

For Azure users who frequently use Azure portal to manage products and services, it might be a 'small surprise' to find out that there is no direct UI feature for adding users to Azure SQL database. When setting up database, there is an option to specify one admin login/account. For some applications, multiple accounts with different access are usually very common. This article shows you the steps to create user for Azure SQL database after the database is created.

Overview

We can use T-SQL directly to create a user for Azure SQL Database with three steps:

• Create a login
• Create a database user for the login
• Grant roles or permissions to the user.

The SQL statements can be run using SqlCmd tool or any other client tools like SQL Server Management Studio or Azure Data Studio.

Create a login

Run the following account to create a login.

CREATE LOGIN asdacct
	WITH PASSWORD = '******'
GO

The above statement creates a login named asdacct with specified password.

Create a database user for the login

Once the login is created, we can create a database user for the login.

USE mydb
GO
CREATE USER asdacct
	FOR LOGIN asdacct
GO

The above statement creates a database user named asdacct for login asdacct in database mydb. Remember to change the database accordingly. 

Add roles for the user

Now we can grant roles and permissions to the user.

USE mydb
GO
EXEC sp_addrolemember N'db_datareader', N'asdacct'
GO

The above statement add role db_datareader to asdacct. For more details about built-in roles, refer to official documentation:

*Image from: Database-Level Roles - SQL Server | Microsoft Docs.

We can also grant individual permission:

USE mydb
GO
GRANT CREATE TABLE TO N'asdacct'
GO

The above statement grants CREATE TABLE permission to user asdacct.