Tutorial: Create and utilize Microsoft Entra server logins

Applies to: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics (dedicated SQL pools only)

This article guides you through creating and utilizing logins backed by Microsoft Entra ID (formerly Azure Active Directory) within the virtual master database of Azure SQL.

In this tutorial, you learn how to:

Prerequisites

• A SQL Database or SQL Managed Instance with a database. See Quickstart: Create an Azure SQL Database single database if you haven't already created an Azure SQL Database, or Quickstart: Create an Azure SQL Managed Instance.
• Microsoft Entra authentication set up for SQL Database or SQL Managed Instance. For more information, see Configure and manage Microsoft Entra authentication with Azure SQL.
• This article instructs you on creating a Microsoft Entra login and user within the virtual master database. Only a Microsoft Entra admin can create a user within the virtual master database, so we recommend you use the Microsoft Entra admin account when going through this tutorial. A Microsoft Entra principal with the loginmanager role can create a login, but not a user within the virtual master database.

Create Microsoft Entra login

1. Create an Azure SQL Database login for a Microsoft Entra account. In our example, we'll use bob@contoso.com that exists in our Microsoft Entra domain called contoso. A login can also be created from a Microsoft Entra group or service principal (applications). For example, mygroup that is a Microsoft Entra group consisting of Microsoft Entra accounts that are a member of that group. For more information, see CREATE LOGIN (Transact-SQL).

   Note

   The first Microsoft Entra login must be created by the Microsoft Entra admin. The Microsoft Entra admin can be a Microsoft Entra user or group. A SQL login can't create Microsoft Entra logins.

   Tip

   If you encounter issues with nonunique display names in Microsoft Entra ID, you can use the WITH OBJECT_ID syntax extension. This feature is particularly useful for service principals with duplicate display names. For example:

   CREATE LOGIN [myapp4466e] FROM EXTERNAL PROVIDER 
     WITH OBJECT_ID = 'aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb'
   

   For more information, see Microsoft Entra logins and users with nonunique display names.

2. Using SQL Server Management Studio (SSMS), log into your SQL Database with the Microsoft Entra admin account set up for the server.

3. Expand Databases > System Databases. Right-click the master database and select New Query to open a new query window in the context of the master database.

4. Run the following query:

   CREATE LOGIN [bob@contoso.com] FROM EXTERNAL PROVIDER
   GO
   

5. Check the created login in sys.server_principals. Execute the following query:

   SELECT name, type_desc, type, is_disabled
   FROM sys.server_principals
   WHERE type_desc like 'external%'
   

   You would see a similar output to the following:

   Name                            type_desc       type   is_disabled
   bob@contoso.com                 EXTERNAL_LOGIN  E      0
   

6. The login bob@contoso.com has been created in the virtual master database.

Create user from a Microsoft Entra login

1. Now that we've created a Microsoft Entra login, we can create a database-level Microsoft Entra user that is mapped to the Microsoft Entra login in the virtual master database. We'll continue to use our example, bob@contoso.com to create a user in the virtual master database, as we want to demonstrate adding the user to special roles. Only a Microsoft Entra admin or SQL server admin can create users in the virtual master database.

2. We're still using the virtual master database, but you can reconnect to a database of your choice if you want to create users in other databases. Run the following query.

   CREATE USER [bob@contoso.com] FROM LOGIN [bob@contoso.com]
   

   Tip

   Although it's not required to use Microsoft Entra user aliases (for example, bob@contoso.com), it is a recommended best practice to use the same alias for Microsoft Entra users and Microsoft Entra logins.

   If you need to create a user directly from an external provider with a specific Object ID (for example, to resolve nonunique display name issues), you can also use:

   CREATE USER [user_name] FROM EXTERNAL PROVIDER 
     WITH OBJECT_ID = 'objectid'
   

3. Check the created user in sys.database_principals. Execute the following query:

   SELECT name, type_desc, type
   FROM sys.database_principals
   WHERE type_desc like 'external%'
   

   You would see a similar output to the following:

   Name                            type_desc       type
   bob@contoso.com                 EXTERNAL_USER   E
   

Grant server-level roles to Microsoft Entra logins

You can add logins to the fixed server-level roles, such as the ##MS_DefinitionReader##, ##MS_ServerStateReader##, or ##MS_ServerStateManager## role in the master database.

ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [AzureAD_object];

ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [AzureAD_object];

ALTER SERVER ROLE ##MS_ServerStateManager## ADD MEMBER [AzureAD_object];

Permissions aren't effective until the user reconnects. Flush the DBCC cache as well:

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

To check which Microsoft Entra logins are part of server-level roles, run the following query:

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       FROM sys.server_role_members AS server_role_members
       INNER JOIN sys.server_principals AS roles
       ON server_role_members.role_principal_id = roles.principal_id
       INNER JOIN sys.server_principals AS members
       ON server_role_members.member_principal_id = members.principal_id;

Grant special roles for Microsoft Entra users

Special roles for SQL Database can be assigned to users in the virtual master database.

In order to grant one of the special database roles to a user, the user must exist in the virtual master database.

To add a user to a role, you can run the following query:

ALTER ROLE [dbmanager] ADD MEMBER [AzureAD_object]

To remove a user from a role, run the following query:

ALTER ROLE [dbmanager] DROP MEMBER [AzureAD_object]

AzureAD_object can be a Microsoft Entra user, group, or service principal in Microsoft Entra ID.

In our example, we created the user bob@contoso.com. Let's give the user the dbmanager and loginmanager roles.

1. Run the following query:

   ALTER ROLE [dbmanager] ADD MEMBER [bob@contoso.com]
   ALTER ROLE [loginmanager] ADD MEMBER [bob@contoso.com]
   

2. Check the database role assignment by running the following query:

   SELECT DP1.name AS DatabaseRoleName,
     isnull (DP2.name, 'No members') AS DatabaseUserName
   FROM sys.database_role_members AS DRM
   RIGHT OUTER JOIN sys.database_principals AS DP1
     ON DRM.role_principal_id = DP1.principal_id
   LEFT OUTER JOIN sys.database_principals AS DP2
     ON DRM.member_principal_id = DP2.principal_id
   WHERE DP1.type = 'R'and DP2.name like 'bob%'
   

   You would see a similar output to the following:

   DatabaseRoleName       DatabaseUserName
   dbmanager              bob@contoso.com
   loginmanager           bob@contoso.com
   

Optional - Disable a login

The ALTER LOGIN (Transact-SQL) DDL syntax can be used to enable or disable a Microsoft Entra login in Azure SQL Database.

ALTER LOGIN [bob@contoso.com] DISABLE

For the DISABLE or ENABLE changes to take immediate effect, the authentication cache and the TokenAndPermUserStore cache must be cleared using the following T-SQL commands:

DBCC FLUSHAUTHCACHE
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS

Check that the login has been disabled by executing the following query:

SELECT name, type_desc, type
FROM sys.server_principals
WHERE is_disabled = 1

A use case for this would be to allow read-only on geo-replicas, but deny connection on a primary server.

Related content

• Microsoft Entra server principals
• Microsoft Entra logins and users with nonunique display names
• Azure SQL Database server roles for permission management
• CREATE LOGIN (Transact-SQL)
• CREATE USER (Transact-SQL)