Adding Users to Azure SQL Databases

By: Joe Gavin   |   Updated: 2021-12-08   |   Comments (19)   |   Related: 1 | 2 | 3 | > Azure SQL Database

Problem

You know how to manage logins and users on traditional on-premises SQL Servers with SQL Server Management Studio (SSMS), but now you’ve created an Azure SQL Database and are now ready to add users.

It’s presumed you are not and will not have users and / or applications using the server level "sa" like server admin credentials chosen when the Azure SQL Database server was built. What is the best way to manage users in Azure SQL Databases?

Solution

Let’s do a quick review. With traditional on-premises SQL Server, you have a login to the SQL Server. The login can be an Active Directory account or created in the SQL Server using local SQL authentication. The login gets you access to the SQL Server only. Then you have a user mapped to the login in individual databases that give you access to a databases(s) with permissions typically granted by putting it in a specific security group(s).

Security at a server / database level with on-premises SQL Server and Azure SQL Database are very similar but you will find some definite differences.

The first difference is the concept of a contained user, which is a user not mapped to a login and authentication is done in Azure Active Directory or in the database itself. Traditional logins to the SQL Server with a user in a database mapped to it still exists, but this breaks from the concept of the required login that gets you access to the server and the user gets you access to the database. Contained users make the database more easily portable.

And the traditional database level roles like db_datareader, db_datawriter, db_ddladmin, etc. are the same, but the traditional server level roles like sysadmin, serveradmin, etc. don’t exist in Azure SQL Database. However, there are two server admin roles, dbmanager (similar to dbcreator) that can create and drop databases, and loginmanager (similar to securityadmin) that can create new logins.

Also, you will immediately notice in SSMS that when you right click on Logins or Users from the Object Explorer and choose New it will open a new query window with the command syntax instead of greeting you with a familiar GUI.

Now that we have reviewed the basics, have some background, know the differences and what to expect, here are our options:

• Create a SQL authentication login and add a user(s) to a database(s) that is mapped to the login. This would be used when you want to manage one login and password for users in multiple databases. This is just like an on-premises SQL Server.
• Create a contained SQL Authentication user in a database(s) not mapped to any login.
• Create a contained Azure Active Directory user for a database(s).
• Create a SQL authentication login, add a user mapped to it in master and add the user to a server level admin role.
• Create a user mapped to an Azure Active Directory user and add the user to a server level admin role.

Examples

Following are examples of our options listed above:

• Connect to your Azure SQL Database server with SSMS as an admin in master. Create a SQL authentication login called ‘test’ with a password of ‘SuperSecret!’, create a user mapped to the login called ‘test’ in a database, and then add it to the db_datareader and db_datawriter roles.

-- create SQL auth login from master 
CREATE LOGIN test 
WITH PASSWORD = 'SuperSecret!' 

• Open another query window and choose your user database in the dropdown.

-- select your db in the dropdown and create a user mapped to a login 
CREATE USER [test] 
FOR LOGIN [test] 
WITH DEFAULT_SCHEMA = dbo; 
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [test]; 
ALTER ROLE db_datawriter ADD MEMBER [test]; 

• Repeat second step for all databases you are adding ‘test’ to. Note, you will need to open a new connection(s).
• Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add the user(s) to in the dropdown. Create a SQL authentication contained user called ‘test’ with a password of ‘SuperSecret!’  then adding it to the db_datareader and db_datawriter roles.

-- select your db in dropdown and create a contained user 
CREATE USER [test] 
WITH PASSWORD = 'SuperSecret!', 
DEFAULT_SCHEMA = dbo; 
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [test]; 
ALTER ROLE db_datawriter ADD MEMBER [test]; 

• Connect to your Azure SQL Database server with SSMS as an admin and choose the database you want to add a user to in the dropdown. Add Azure Active Directory user ‘[email protected]’ then add it to the db_datareader and db_datawriter roles. 

-- add contained Azure AD user 
CREATE USER [[email protected]] 
FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = dbo;  
  
-- add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [test]; 
ALTER ROLE db_datawriter ADD MEMBER [test]; 

• Next, we'll create a login and user in master that can manage databases and logins. Connect to your Azure SQL Database server with SSMS as an admin in master. Add a SQL authentication login called ‘test2’ and a password of ‘SuperSecret!’, create a user mapped to it in master and add the user to the dbmanager and login manager roles.

-- add login  
CREATE LOGIN [test2] 
WITH PASSWORD='SuperSecret!';  
  
-- add user 
CREATE USER [test2] 
FROM LOGIN [test2] 
WITH DEFAULT_SCHEMA=dbo; 
  
-- add user to role(s) in db 
ALTER ROLE dbmanager ADD MEMBER [test2]; 
ALTER ROLE loginmanager ADD MEMBER [test2]; 

• Lastly, we'll add an Azure Active Directory user that can also manage databases and logins. Connect to your Azure SQL Database server with SSMS as an admin in master. Add a contained user ‘[email protected]’ and add it to the dbmanager and login manager roles.

-- add contained Azure AD user 
CREATE USER [[email protected]] 
FROM EXTERNAL PROVIDER 
WITH DEFAULT_SCHEMA = dbo;  
  
-- add user to role(s) in db 
ALTER ROLE dbmanager ADD MEMBER [[email protected]]; 
ALTER ROLE loginmanager ADD MEMBER [[email protected]]; 

Next Steps

Hopefully this tip has given you everything you need to know to manage logins and users in SQL Azure Database but you can find further info here:

• https://docs.microsoft.com/en-us/azure/sql-database/
• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
• https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/
• https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user
• https://blogs.msdn.microsoft.com/azuresqldbsupport/2016/10/05/create-sql-login-and-sql-user-on-your-azure-sql-db/

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips