Adding Users to Azure SQL Databases


By:   |   Updated: 2018-01-05   |   Comments (13)   |   Related: More > Azure


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.

create login syntax screenshot
create user syntax screenshot

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 dbmanager ADD MEMBER [[email protected]]; 
ALTER ROLE loginmanager ADD MEMBER [[email protected]]; 
  • 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]; 
  • 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:



Last Updated: 2018-01-05


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources





Comments For This Article




Friday, November 13, 2020 - 8:45:31 AM - Joe Gavin Back To Top (87798)
Anne, you would need to be in as a server admin or in loginmanager to execute 'CREATE LOGIN' and a server admin, loginmanager or db_owner in the database you're executing a 'CREATE USER' in.

Thursday, November 12, 2020 - 2:47:51 PM - Anne Cao Back To Top (87793)
Sorry I meant db_datareader and db_dataWriter in my above post.

Also for these SQL statements, it has to be server admin role to do these, correct?

Thursday, November 12, 2020 - 2:45:02 PM - Joe Gavin Back To Top (87792)
Thanks for catching that Anne. You are correct. Looks like I had a little copy/paste mishap when I was testing.

Thursday, November 12, 2020 - 1:09:35 PM - Anne Cao Back To Top (87791)
Thanks for the nice article, it is very helpful.
One thing I am not clear is the third yellow paragrah from bottom:
-- 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]];

Should the last two lines be:
ALTER ROLE db_reader ADD MEMBER [[email protected]];
ALTER ROLE db_writer ADD MEMBER [[email protected]];

Thursday, October 29, 2020 - 7:29:46 AM - Joe Gavin Back To Top (86713)
Hi Sandeep. You may want to look at restricting access via firewall.

Wednesday, October 28, 2020 - 3:23:10 PM - Sandeep Back To Top (86709)
Hi,

I created SQL account for application to connect to database and the connection string is placed in web.config file. Since, the user name and password is visible in config file is there a way for me to restrict that particular account not to connect using ssms or azure data studio.


Note: I was able to do in onpremise but not in azure sql database ( paas cloud )
Thank You.

Tuesday, January 14, 2020 - 2:02:03 AM - Saumya Suhagiya Back To Top (83759)

Thanks! Worked like a charm in Azure SQL DB


Monday, October 21, 2019 - 9:36:01 AM - René Imthorn Back To Top (82844)

Thanks!


Monday, July 29, 2019 - 11:22:11 AM - Carsten Pedersen Back To Top (81893)

Thanks very much! Very consicely written and exactly what I needed.


Wednesday, January 23, 2019 - 1:09:23 PM - granadacoder Back To Top (78855)

THANKS!

//Connect to your Azure SQL Database server with SSMS as an admin in master.//

That is what I was missing.  You cannot use the "Use Master" (or "Use MyOtherDatabase").....so you have to direct connect to master.

Now I'm up and running!


Monday, April 09, 2018 - 3:53:14 PM - Joe Gavin Back To Top (75650)

Marios, I read up on this a bit and it doesn't appear you can do something like this.  

 


Thursday, March 29, 2018 - 3:48:34 PM - Joe Gavin Back To Top (75555)

 Marios, good question and I'm curious now as well. I'll see what I can find for you.


Wednesday, March 28, 2018 - 11:20:54 AM - Marios Philippopoulos Back To Top (75547)

Hi,

I have a question regarding failover groups in Azure.

I have a failover-group configuration of an azure db, where the prod copy is on server A and the secondary (read-only) copy is on server B.

I have created and added a user to db_datareader on a db in server A (in Azure); the same user is automatically replicated with the same role membership on server B in the failover group.

Now I would like to disable access of that user to the primary (prod) db on server A, and allow access on the secondary copy of the db on server B only.

However, this command is not supported in SQL; only works with logins, not users:

ALTER USER [user1] DISABLE;

How can we restrict access of contained-db users in Azure on the secondary replica of a failover-group configuration in Azure?

Thanks!

Marios



download





Recommended Reading

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

Continuous database deployments with Azure DevOps

Process Blob Files Automatically using an Azure Function with Blob Trigger

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks














get free sql tips
agree to terms