How to create SQL authentication user in Azure SQL database

When you create an Azure SQL database, a default administrator user is created. This user has all the administrative rights and can manage the SQL server and the databases as an administrator.

However, you also need to create more users to manage access to the database. We use multiple users with different authorization levels and permissions to access the database.

Hence, in this Azure SQL tutorial, we will explain the different types of users, authentication, and authorization mechanisms with some examples. You will see various ways to create a user in the Azure SQL database using SQL authentication.

• Create login in Azure SQL server
• Create contained user in Azure SQL database
• Azure SQL create contained database user example
• Azure SQL create user and login
• Azure SQL create dbo user
• Azure SQL create user db_owner
• Azure SQL create user read only
• Azure SQL create user with password
• Azure SQL create user without login
• Azure SQL add user to sysadmin role
• Azure SQL list users and roles
• Azure SQL create user if not exists
• Azure SQL add user to role
• Azure SQL database create user in master
• Azure SQL create user with default database

Create login in Azure SQL server

In this approach, you create a login account in the Azure SQL master database first and then create a user account in the database(s). Once you have created both accounts, you need to associate them.

In this approach, you can also create multiple user accounts in the databases and associate them all to one login account in the master database. The result is that the password is synchronized for all users.

You can use this approach if you want multiple users to access multiple databases but want the same synchronized password for all the users.

There is one thing to be noticed that the authentication information is stored in the master database only. Thus, if you are using this approach in a geo-replicated database, you have to create this login information on all the servers separately, which is a complex task.

We will explain how to create a user using this approach later in this tutorial. But, let us understand the second approach for creating a user in the Azure SQL database.

Read Pause and resume Azure SQL database

Create contained user in Azure SQL database

A contained database is a database that is isolated from the other databases and the master database. You do not associate this type of user account with an existing login account.

Instead, you can just create a user, set a password, and assign the desired roles and permissions to the users.

You can use this approach if you want to create a user that is not associated with any login account and uses its own login information to access the database.

However, this approach can become complex as you have to synchronize the passwords manually at your own end.

One benefit of this approach is that when you create a contained user, the login information is stored in the database itself.

Thus, if you are using the database with the geo-replication feature, you do not need to create this user information on all databases. All the user information is replicated along with the replicated database.

Read How to Connect to Azure SQL database

Azure SQL create contained database user example

As you are familiar with the contained database user in the Azure SQL database, you will learn how you can create a contained database user in the Azure SQL database. We will explain this with the help of an example for better understanding.

We create a contained database user using the CREATE USER statement. The general syntax for the CREATE USER statement is:

CREATE USER <username> WITH <options>

Now let us create a user in our database.

• Connect to your database using the SQL server management studio as an admin user.
• Expand the database in the object explorer window and go to Security>> Users.
• You can see the list of users in your database.
• To create a new user, right-click on Users, and click on New User.

• You will get a template for the CREATE USER statement. The template will look like:

-- =======================================================================================
-- Create User as DBO template for Azure SQL Database and Azure Synapse Analytics Database
-- =======================================================================================
-- For login <login_name, sysname, login_name>, create a user in the database
CREATE USER <user_name, sysname, user_name>
	FOR LOGIN <login_name, sysname, login_name>
	WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>'
GO

• You can alter this template according to your requirements.
• Firstly, you will create a user. Then you will add the user in any database role.
• For example, we will create a user named NewUser and add this user to the database owner role. We will set the password for the user as Test@123.

-- =======================================================================
-- Create the user
CREATE USER NewUser
	WITH PASSWORD= 'Test@123'
-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'NewUser'
GO
-- =======================================================================

• Once you have executed the above query, you can refresh the users in the object explorer window and see the new user.
• You can disconnect the database and connect to it again with the new user.
• While connecting to the database, you may face the following error:

• This is because the user is trying to connect to the default database i.e. the master database. A contained user cannot connect to the master database. You have to specify the database name to whoch you want to connect.
• Click on Options in the Connect to SQL Server dialog box.
• In the Connection Propertties tab, specify the database name in the the Connect to database option. Click on Connect.

After connecting to the database, you can see the database in the object explorer window.

Read Cannot open server requested by the login

Azure SQL create user and login

The traditional approach for creating a user in any SQL Server database is to first create a login in the database. After that, we create the user and associate this user to the login in the database.

You can associate multiple users with a single login. Thus, the password will be the same for all while logging in to the database.

We have created an example of this type of user. You can follow the below approach to creating a user with a login.

• Firstly, connect to your Azure SQL database as an administrator.
• Create a new login with the CREATE LOGIN statement. This login information will be stored in the master database. The general syntax of the CREATE LOGIN statement is:

CREATE LOGIN <login_name> WITH <options>

• There are two options that you can use with this statement:
  • PASSWORD: Specify a password for the login
  • SID: Create the login with an SID. If the SID is not specified, Azure SQL creates one automatically for the login.
• After creating the login, we create a user account in the database with the CREATE USER statement and associate it with an existing login. The general syntax is:

CREATE USER <username> FOR LOGIN <login_name> WITH <options>

• Now let us see an example. We will create a login, create a user and associate the user with the login.
• Firstly, we will create a login named as NewLogin with a password as Test@123. Make sure you are executing this query on the master database.

CREATE LOGIN NewLogin WITH PASSWORD = 'Test@123'

• Switch from the master database to your database in which you want to create a new database user.

• Then we will create a user and assosciate that user with the NewLogin account.

CREATE USER User1 FOR LOGIN NewLogin

• Now you can connect to the database with the new login and verify the new user.

Thus, you might have learned how you can create a login and a user in an Azure SQL database.

Read How to create table in azure sql database

Azure SQL create dbo user

When you create an Azure SQL database, a default dbo user is also created which is the owner of the database. You can create a user and make it the owner of the database by assigning the role db_owner to it.

Read the below section to create a user and assign the role db_owner to it in an Azure SQL database.

Azure SQL create user db_owner

A db_owner is a fixed database role that can perform various activities on the database. For example, maintenance of the database. This role can also drop the database.

In this section, we will create a user and assign the db_owner role to it. You can follow the below steps to create such a user:

• Firstly, we will create a contained user in our database with the CREATE USER statement. RIght click on your database in the object explorer window and click on New Query. Write the below query in the query editor window.:

CREATE USER user_owner WITH PASSWORD= 'Test@123'

• Secondly, we will asssign our user the role of db_owner using the ALTER ROLE statement.

ALTER ROLE db_owner ADD MEMBER user_owner

• Now you can use the below T-SQL qyery to verify if the user is assigned the role or not.

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u 
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id 
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id 
WHERE u.type NOT IN('R', 'G') 
ORDER BY UserName desc, RoleName;

• You can see in the below image that the user is assgined the role of db_owner.

Thus, you might have learned how you can create a user in the Azure SQL database with the db_owner role.

Read How to rename a database in Azure SQL

Azure SQL create user read only

A read-only user is a user in the database that has permission to only read the data in the database. It can only access the data in the database. It cannot manage the database.

You can create a user in the Azure SQL database and assign the role db_datareader to the user to give read-only access.

For example, we will create a contained user in our Azure SQL database that will be a read-only user.

• Firstly, we will create a contained user with the CREATE USER statement:

CREATE USER read_only_user WITH PASSWORD= 'Test@123'

• Now we will assign this user the role db_datareader:

EXEC sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'read_only_user'

• You can verify if the desired role is assigned to the user or not by executing the below T-SQL query:

SELECT T1.name AS RoleName,   
   isnull (T2.name, 'No members') AS UserName   
 FROM sys.database_role_members AS T3  
 RIGHT OUTER JOIN sys.database_principals AS T1  
   ON T3.role_principal_id = T1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS T2  
   ON T3.member_principal_id = T2.principal_id  
WHERE T1.type = 'R'
ORDER BY T1.name; 

• You can see that the role of db_datareader is assigned to the user read_only_user.

Thus, you might have learned how you can create a read-only user in an Azure SQL database,

Read Backup and restore SQL Server to Azure Blob storage

Azure SQL create user with password

You can create a user in an Azure SQL database with a password. When you create a user with the CREATE USER statement, there is an option of WITH PASSWORD that you can use to define the password for the user account.

You can create a user with a password as:

CREATE USER <username> WITH PASSWORD= <password>

• For example, if you want to create a user named as DemoUser with the password Demo@123, you will write the T-SQL query as:

CREATE USER DemoUser WITH PASSWORD= 'Demo@123'

• Make sure to comply with the password policy for defining a password. There are some constraints that you have to follow while defining the password.

Hence, in this way you can use the WITH PASSWORD option to create a user with a password.

Read Read only replica Azure SQL

Azure SQL create user without login

In the Azure SQL database, you can also create a user without login. This type of user does not authenticate to the database. But you can assign different roles and permissions to it.

There are some reasons behind creating this type of user. Sometimes, we want a user that does not authenticate to the database. But at the same time, it can perform some operations in the database.

In such situations, we create a user without login, assign the desired roles and permissions and use the EXECUTE AS <user> statement to run any T-SQL code with this user’s permissions.

You can use the WITHOUT LOGIN option in the CREATE USER statement to create a user without login. Below is the syntax:

CREATE USER <username> WIHTOUT LOGIN [,Other options]

• For example, we will create a user wihtout login and assign the db_datareader role to it to give it read-only access.

CREATE USER UserWithoutLogin WITHOUT LOGIN
EXEC sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'UserWithoutLogin'

Hence, in this way you can create a user without login in Azure SQL.

Read Backup Azure database to local SQL Server

Azure SQL add user to sysadmin role

In Azure SQL you cannot add a user to the sysadmin role. Because this role is not available in the Azure SQL database.

This is because you are using Platform as a Service (PaaS) and not an on-premise SQL Server. You cannot have multiple admin accounts with full administrative privileges.

When you create an Azure SQL database, you provide an admin login name and a password. This user account has all the administrative permissions and it is called Server Admin.

You cannot create a user with full administrative privileges for SQL authentication. But you can create an Active Directory user with full administrative privileges.

Azure SQL list users and roles

The information about roles in the database is stored in the sys.database_principals catalog view. And, the information about users associated with a role is stored in the sys.database_role_members.

You can execute the below query to return a list of usernames and the roles associated with them:

SELECT T1.name AS RoleName,   
   isnull (T2.name, 'No members') AS UserName   
 FROM sys.database_role_members AS T3  
 RIGHT OUTER JOIN sys.database_principals AS T1  
   ON T3.role_principal_id = T1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS T2  
   ON T3.member_principal_id = T2.principal_id  
WHERE T1.type = 'R'
ORDER BY T1.name; 

Hence, in this way you can list the usernames and the roles associated with them in an Azure SQL database.

Read Reset Password Azure SQL database

Azure SQL create user if not exists

The user information is stored in the sys.sysusers catalog view. You can use the IF NOT EXISTS statement to check if the user exists in the database or not. You just need to query the sys.sysusers catalog view in the database.

IF NOT EXISTS (SELECT name FROM sys.sysusers WHERE name= <username>
    BEGIN
        CREATE USER <username> WITH PASSWORD= <password>;
    END

• For example, we are trying to create a user DemoUser only if it does not exist in the database. Therefore, we will run the below query to check if it exists in the database.
• If it does not exist in the database, only then it will be created, otherwise the CREATE USER statement will be ignored.

IF NOT EXISTS (SELECT name FROM sys.sysusers WHERE name= 'DemoUser')
    BEGIN
        CREATE USER DemoUser WITH PASSWORD= 'Test@123';
    END

In this way, you can create a user in the database only if it does not exist in the database.

Azure SQL add user to role

Once you have created a user in an Azure SQL database, you need to give some authority to the user in the database. For example, read, write or manage the database.

To give this authority to a user we assign roles to the user. There are some predefined roles in the Azure SQL database or you can also create your own.

To add a user to a role, there are two methods that you can use:

1. Execute the stored procedure sp_addrolemember
2. Using the ALTER ROLE statement

Using sp_addrolemember:

You can execute the sp_addrolemember stored procedure to add a user to any role in the Azure SQL database. The syntax is:

sp_addrolemember @rolename =<role_name>, @membername = <user_name>

• For example, we have user named as TestUser in our database.
• If we want to add this user to the db_datareader role in the database, we will execute the sp_addrolemember as:

sp_addrolemember @rolename ='db_datareader', @membername = 'Testuser'

• Similarly, you can add any user to any database role using this sp_addrolemember stored procedure.

Using the ALTER ROLE statement:

Microsoft suggests using this statement to assign roles to the user. The syntax of the ALTER ROLE statement is:

ALTER ROLE  role_name  
ADD MEMBER database_principal

• For example, if I have a user named as DemoUser in the database and I want to add it to the db_datawriter role, I will execute the statement as:

ALTER ROLE  db_datawriter   
	ADD MEMBER DemoUser

• You can also see a list of users and the roles associated with them using the below SQL query;

SELECT T1.name AS RoleName,   
   isnull (T2.name, 'No members') AS UserName   
 FROM sys.database_role_members AS T3  
 RIGHT OUTER JOIN sys.database_principals AS T1  
   ON T3.role_principal_id = T1.principal_id  
 LEFT OUTER JOIN sys.database_principals AS T2  
   ON T3.member_principal_id = T2.principal_id  
WHERE T1.type = 'R'
ORDER BY T1.name; 

Thus, in this way you can add any user to any database role in the Azure SQL database.

Read Cannot open backup device operating system error 50 azure

Azure SQL database create user in master

In this section, we will explain how to create a user in the master database in an Azure SQL database.

You cannot execute queries on the master database from the Azure portal. You need to connect to the Azure SQL database using any IDE like Azure Data Studio or SQL Server management studio.

We will use SQL Server management studio to demonstrate how you can create a user in the master database in the Azure SQL database.

• Connect to your Azure SQL database.
• Click on New Query and make sure that you are executing the query on the master database.
• You can switch to the master database in SQL Server mamagement studio. See the below image for reference.

• Now you can create the user in this database using the CREATE USER statement:

CREATE USER <username> WITH PASSWORD= <password>

• For example, to create a user named as MasterUser with password as Test@123, we will execute the statement as:

CREATE USER MasterUser WITH PASSWORD= 'Test@123'

• You can verify in the object explorer window if the user is created or not. Expand your database and open Security >> Users.

Thus, you might have learned how you can create a user in the master database.

Read SQL Server stored procedure if exists update else insert

Azure SQL create user with default database

In the Azure SQL database, if you want to create a user which connects to a default database, you can create that user as a contained user. There is no option to create a user with a default database except the user is a contained user.

When you create a contained user in a database, the user can log in to that database only and not in another database.

To create a contained user, you write the CREATE USER statement as:

CREATE USER <username> WITH PASSWORD= <password>

Let us see an example.

• Consider a database named as DemoDatabase.
• We want to create a user that connects to this database only.
• To do this, connect to the database with SQL Server management studio.
• Right click on the database in the object explorer window, and click on New Query.
• A query editor window will be opened where you will execute the queries specifically in your database.

• We will create a user named DefaultUser on this database.

CREATE USER DefaultUser WITH PASSWORD ='Test@123'

• Once we have created the user, we will connect to this database as the new user.
• In the Connect to Server dialog box in SQL Server management studio, click on Options>>>.
• In the Connection Properties tab. select the option Connect to database as the database in which you created the user.

• Now you can click on Connect, to connect to the database.

Related Posts:

• SQL Server check user permissions on table
• How to execute stored procedure in SQL Server
• SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’
• How to get list of users in SQL Server
• How to check if SQL Server is running

Thus, in this way you can create a user with the default database in Azure SQL.

• Create login in Azure SQL server
• Create contained user in Azure SQL database
• Azure SQL create contained database user example
• Azure SQL create user and login
• Azure SQL create dbo user
• Azure SQL create user db_owner
• Azure SQL create user read only
• Azure SQL create user with password
• Azure SQL create user without login
• Azure SQL add user to sysadmin role
• Azure SQL list users and roles
• Azure SQL create user if not exists
• Azure SQL add user to role
• Azure SQL database create user in master
• Azure SQL create user with default database