Working with Azure SQL Server, is very similar to working with the traditional on-premise SQL Server. However when it comes to managing users some differences start to become apparent.

The first noticeable difference is when trying to create a new login or user. The traditional and user friendly dialog will not appear but instead a Query window will show the create user syntax.

The second difference is that it is not possible to switch between databases using the USE statement. But instead a new connection is required. Either by switching the Query Window database link if using the SQL Server Management Studio (SSMS) or if doing it from the Azure portal by changing the database window.

So, how to go about creating a new user?

Below are the 4 steps required to create a new user.

Step 1: Connect to the Azure SQL Server using SSMS and the administrator credentials.

To create new users for the Azure SQL Server, access to the master database is required. As the master database is not available in the Azure portal and the USE command cannot be used. Using SSMS is ideal.

Step 2: Create the Login credentials

Although the user can be created at the database level with the login details, it is recommended to create a login credential which then can be referenced by the user in the database.

To create the Login credentials make sure to be connected with the master database and run the the below command to get the login created

CREATE LOGIN <NEW username>
   WITH Password = '<some password>'
 
GO

Note: The password will be checked against the database policy for password requirements.

Step 3: Create the User in the master database

To allow users to connect with the Azure SQL Server using the new login credentials, a corresponding user needs to be created in the master database.

CREATE USER <username>
  FOR LOGIN <NEW username>
  WITH DEFAULT_SCHEMA = dbo
 
GO

The username for the database user doesn’t need to match the login username. However the username used in the FOR LOGIN line must match the username used in Step 2.

Furthermore, to allow the user to access any of the other databases it is important to assign the dbo schema as default.

Step 4: Give access to a specific database

If a user attempts to log with the new credentials, they will succeed to connect. However they will fail to access any of the database besides the master. This happens as no user has been created for the other databases that correspond to the login credentials created.

For each database that needs to be accessible by the new credentials, a user needs to be created. This is achieved by opening the context menu on the database desired and selecting the New Query option. In the new window that opens up, the following command can be executed to grant access.

CREATE USER 
  FOR LOGIN 
  WITH DEFAULT_SCHEMA = dbo
GO
EXEC sp_addrolemember 'db_owner', '<username>'
GO

Once Step 4 is repeated for each database of interest, users will be able to access the database using the new credentials.