Creating logins and users in Azure Database

Azure Database is the PaaS solution for SQL Server databases, on a previous post we have discussed how to create one.

On this post, I want to show you how you can secure your Azure SQL Database by creating users and segregating their permissions.

When you connect to your Azure Database using SSMS (or another tool), you can see the management options are very limited compared to an On-Premises instance.

If you want to create a login and database user, you must create them via T-SQL, on this post I will show you how to do it.

Types of logins

Azure SQL database support two types of logins: SQL Server login and Azure Active directory login.

In order to create Azure AD logins, you must set up an AD administrator first using the Azure portal, you configure it on the server dashboard, then accessing the Active Directory Admin, as follows:

Once you set up your AD Admin, you can connect to the Azure database using this account and you can then assign proper access to other AD accounts.

Creating logins and users

As we told you before, you must create users and assign permissions using T-SQL, the basic script for creating users will be as follows (this is for SQL logins):

Note that as PaaS, you connect only to one database, so, the USE <database> command is not supported on Azure databases, so you must run the T-SQL script connecting to the required database manually:

Script #1 for creating the login

/******* Run This code on the MASTER Database ******/

-- Create login,

CREATE LOGIN az_read
 WITH PASSWORD = 'YourStrongP@ssW0rd' 
GO


-- Create user in master database (so the user can connect using ssms or ADS)
CREATE USER az_read
 FOR LOGIN az_read
 WITH DEFAULT_SCHEMA = dbo
GO

Script #2 - For the user database you want to provide access:

/******* Run This code on the Database you want to give the access ******/

-- The user database where you want to give the access

CREATE USER az_read
 FOR LOGIN az_read
 WITH DEFAULT_SCHEMA = dbo
GO

-- Add user to the database roles you want
EXEC sp_addrolemember N'db_datareader', N'sqlreadusr'
GO

Explaining it:

You first need to create the login, and set up your password, following the Azure strong password requirements.
Then, if the user is planning to connect to the instance using SSMS or ADS or another tool where the default database to connect is not required,  you must create the user in the master database (without roles, unless required specific access).
Next step is to create the user on the database you want to provide the access.
Finally, you assign the roles you want for that particular user.

After that you can connect with the user to provide the respective access:

For creating logins from Azure Active Directory, the script changes a little, you must create the login connecting to the database using another AD account (the administrator we configurated earlier or another AD user with enough privileges), then you specify the AD account followed by FROM EXTERNAL PROVIDER.

Once you are connected you must only change the first script, as follows:

/******* Run This code on the MASTER Database ******/

-- Create login,

CREATE USER [epivaral@galileo.edu] FROM EXTERNAL PROVIDER;
GO

-- Create user in master database (so the user can connect using ssms or ADS)
CREATE USER [epivaral@galileo.edu]
 FOR LOGIN [epivaral@galileo.edu]
 WITH DEFAULT_SCHEMA = dbo
GO

There is no change in script 2 to provide access to the user on a specific database.

Contained User

For more secure environments, you can create contained database users, this approach provide you a more portable database, with no need to worry about the logins, this is the recommended way to grant users access to Azure databases.

In order to create a contained user, just connect to the database you want to provide the access, and run the create user script as follows:

/**** Run the script on the Azure database you want to grant access *****/

CREATE USER contained_user 
WITH PASSWORD = 'YourStrongP@ssW0rd';

GO

After creating the contained user, you can use it by specifying the database you want to connect in the connection options (for this example using Azure Data Studio):

You can see in the object explorer, we only have access to the database we connected, improving security and portability:

You can read more about this in the Microsoft official documentation here.