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.
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.