Domain 2 of 5 · Chapter 1 of 3

Configure database authentication and authorization

Two checks on every connection

A reporting service on an Azure virtual machine connects to Azure SQL Database with the connection string below, and two separate checks must pass before its first query returns a row.

Connection string: a managed identity connecting to Azure SQL Database through ADO.NET SqlClient

Server=tcp:contoso.database.windows.net,1433;Database=<database>;Authentication=Active Directory Managed Identity;Encrypt=true;...

The Authentication=Active Directory Managed Identity keyword tells the driver to use the system-assigned or user-assigned managed identity available to the application host, and the trailing ... marks options omitted here (Connect to Azure SQL with Microsoft Entra authentication and SqlClient[1]). The authentication and authorization checks that follow are the subject of this topic.

The first check is authentication, the process of proving the caller is who it claims to be: the caller presents a credential, and the server matches it to an account (Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics[2]). In SQL terms that account is a principal, an entity that can request database resources, and every principal has a security identifier (SID) (Principals (Database Engine)[3]). The second check is authorization: the permissions assigned to the principal determine what it is allowed to do, and they come from database role memberships and object-level permissions (Authorize database access[2]). Every permission applies to a securable, the item being protected, such as the server, a database, a schema or a table (Permissions (Database Engine)[4]).

The figure below traces the path. A credential is authenticated to a principal; inside a database, that principal acts through a database user, either one mapped from its login or a contained user that has no login (both are defined in Principals at server and database scope below); authorization then decides each statement. Two fallbacks let a login act in a database without its own user: the guest user, covered in public and guest below, and Azure SQL Database's ##MS_DatabaseConnector## server role, which grants CONNECT to every database and is covered in Server-level roles below (Azure SQL Database server roles for permission management[5]).

Neither check involves Azure role-based access control (Azure RBAC). Microsoft Entra authentication for Azure SQL doesn't integrate with Azure RBAC, and the SQL Server Contributor and SQL DB Contributor roles secure management-related deployment operations, not database connectivity (Microsoft Entra authentication for Azure SQL[6]). The separation runs both ways: those Azure roles authorize management operations on the Azure resource rather than queries, and a database user's SELECT permission doesn't let the same identity resize the Azure resource (Azure built-in roles for Databases[7]). An operations role that needs both kinds of access gets each one delegated separately.

This topic covers both checks on Azure SQL Database, Azure SQL Managed Instance and SQL Server on Azure VMs, which the Microsoft DP-300 study guide[8] lists under Configure database authentication and authorization with bullets such as 'Configure security principals'. Deciding whether a connection reaches the server is a different task: in Azure SQL Database, connection attempts from the internet and Azure must pass through the firewall before they reach the server or database (Azure SQL Database IP firewall rules[9]), and the study guide[8] lists 'Configure server- and database-level firewall rules' under Implement security for data at rest and data in transit. Controlling which table rows a user can access, based on who is running the query (for example, their group membership), is another task (Row-level security[10]), and the study guide[8] lists 'Implement row-level security' under Implement compliance controls for sensitive data.

A connection therefore needs a principal the engine can authenticate and permissions that authorize each statement, and an Azure role assignment supplies neither.

Credential sentby the clientAuthentication:match a principalDatabase user:mapped from a loginor containedAuthorization:permissions on securablesStatement runsAzure RBAC:manages theAzure resource,not this path
The two checks on a database connection: authentication to a principal, then authorization of each statement through permissions on securables

Principals at server and database scope

Principals are defined at a scope, and the scope decides where a principal lives and what it can be granted (Principals (Database Engine)[3]). This topic works with the following principals at the two engine scopes (the vendor list also includes application roles):

Scope Principals used in this topic
Server level Logins (SQL authentication; Windows user or group; Microsoft Entra user, group or service principal) and server roles
Database level Database users and database roles

A login is an account in the master database that authenticates the connection, and a database user in one or more databases can be linked to it (Authorize database access[2]). A database user is the principal inside one database to which roles and object permissions are assigned. The login authenticates at server scope and the mapped user supplies the database identity; for explicit login-to-user mapping, run CREATE USER [AppUser] FOR LOGIN [AppLogin] in each database the login needs (CREATE USER (Transact-SQL)[11]). One login can map to users in several databases, but to only one user in each database (CREATE USER (Transact-SQL)[11]), and the two names can differ because the mapping is stored in the database as the login's SID, not its name (Troubleshoot orphaned users[12]).

A contained database user is a database user without a login: the credential (a password, or a Microsoft Entra identity) is stored with the user account in the database itself (Authorize database access[2]). Contained database users below covers when to choose one.

Here the server means a SQL Server instance, a managed instance, or, for Azure SQL Database, the logical server, which is a logical concept rather than a machine you manage (Azure SQL Database server roles for permission management[5]). The figure below draws the two scopes as nested boxes: the server holds logins and server roles in master, each user database holds its users and database roles, and a login-mapped user points back to its login by SID while a contained user has no login to point to.

Roles exist at both scopes and never cross between them. Logins and other server-level principals can't be added to database roles, and server-level permissions can't be granted to database roles (Database-level roles[13]). A database role therefore groups database principals only: ALTER ROLE ... ADD MEMBER takes a database user, never a login. Azure SQL Database has the same database permission system as SQL Server but no grantable server-level permissions (Permissions (Database Engine)[4]); in their place it offers fixed server-level roles, covered in Server-level roles below (Azure SQL Database server roles for permission management[5]).

The login answers "may this credential connect to the server?", the database user answers "who is this caller in this database?", and each kind of role groups principals from its own scope only.

Server (instance or logical server)master databaseLoginsServer rolesUser databaseLogin-mappeduserContained user(no login)Database roles:group database principalsmapped by SID
Principals by scope: logins and server roles in master, users and database roles in each user database, with login-mapped users linked by SID

Microsoft Entra authentication for Azure SQL

This section covers how Microsoft Entra ID (formerly Azure Active Directory) becomes a working authentication method for Azure SQL Database and Azure SQL Managed Instance. With Microsoft Entra authentication, the caller asks the service to use credential information stored in Microsoft Entra ID instead of a password stored by SQL (Authorize database access[2]). Azure SQL accepts Microsoft Entra users (internal and external users, guests and members), applications that authenticate with service principals or managed identities, and Microsoft Entra groups as principals (Microsoft Entra authentication for Azure SQL[6]).

The general configuration steps

Microsoft lists six general steps, traced in the figure below: create and populate a Microsoft Entra tenant; create a logical server or instance in Azure; assign a Microsoft Entra administrator to the server or instance; create SQL principals in your databases that are mapped to Microsoft Entra identities; configure client applications to connect with Azure Identity libraries and authentication methods; and connect with Microsoft Entra identities (Microsoft Entra authentication for Azure SQL[6]). A logical server is the Azure resource whose master database holds the logins for its Azure SQL databases (Authorize database access[2]).

The Microsoft Entra administrator

Step 3 gates everything after it. To enable Microsoft Entra authentication, a Microsoft Entra administrator has to be set for the logical server or managed instance, and it exists alongside the SQL server administrator created at deployment (Microsoft Entra authentication for Azure SQL[6]). The property is singular, not a list: it holds one identity at a time, which can be a user, group, service principal or managed identity. Setting it to a group lets every member of the group act as the administrator, which moves administrator management into Microsoft Entra group membership. The administrator is the first account that can create other Microsoft Entra logins and users, and it enters each user database as the dbo user, a member of the db_owner role (Microsoft Entra authentication for Azure SQL[6]). The dbo user is the special database-owner principal that has all permissions in its database and can't be limited (Principals (Database Engine)[3]).

Setting this property is not an Azure role assignment. Giving the intended administrator the SQL Server Contributor role delegates management of the Azure resource and leaves the server's Microsoft Entra administrator unset, because Azure SQL's Microsoft Entra authentication doesn't integrate with Azure RBAC (see Two checks on every connection).

Removing the Microsoft Entra administrator disables all Microsoft Entra authentication-based connections, even for existing Microsoft Entra users who still have permissions in a database (Microsoft Entra authentication for Azure SQL[6]). The users and their grants remain; the authentication path they depend on is gone.

Identities from another tenant

You can't directly create a database user for an identity managed in a different Microsoft Entra tenant from the one associated with your Azure subscription (Configure and manage Microsoft Entra authentication with Azure SQL[14]). Import that identity into the associated tenant as an external user and then create its contained database user, or give it access through membership in a Microsoft Entra group that has a database user.

Microsoft Entra authentication on Azure SQL therefore starts with one administrator property: set it before anything else, never remove it casually, and treat it as separate from any Azure role.

1. Create and populate the tenant2. Create a logical server or instance3. Assign a Microsoft Entra administrator4. Create SQL principals for identities5. Configure client authentication6. Connect as a Microsoft Entra identity
Microsoft's six general steps to configure Microsoft Entra authentication for Azure SQL; step 3, the Microsoft Entra administrator, gates the rest

Choosing a Microsoft Entra authentication method

Once a server accepts Microsoft Entra identities, each client still has to decide how it obtains its token, and the right method depends on whether a person or a workload is connecting. Microsoft groups the supported methods into methods for user identities and methods for service or workload identities (Microsoft Entra authentication for Azure SQL[6]). The table below names them with their ADO.NET SqlClient Authentication keywords (Connect to Azure SQL with Microsoft Entra authentication and SqlClient[1]); the list is partial, and the same source documents further modes such as device code flow and Active Directory Default. SqlClient is one client driver among several, and other clients name the same methods differently, as Universal with MFA below shows.

Caller Authentication keyword What it does
A person at a desktop or developer application Active Directory Interactive Prompts the user to sign in and supports multifactor authentication (MFA)
A person on a domain-joined, federated machine Active Directory Integrated Acquires a token with the signed-in domain identity; can't satisfy an interactive MFA challenge
A workload hosted in Azure Active Directory Managed Identity Uses the system-assigned or user-assigned managed identity of the host
Another unattended workload Active Directory Service Principal Uses the application's client ID and client secret
None: avoid for new connections Active Directory Password Deprecated user name and password flow; can't satisfy MFA

People who must pass MFA

A person subject to MFA uses an interactive method: Active Directory Interactive prompts the user and supports MFA (Connect to Azure SQL with Microsoft Entra authentication and SqlClient[1]), and SQL Server lists the same capability as Universal with MFA (Enable Microsoft Entra authentication for SQL Server on Azure VMs[15]). Microsoft recommends configuring MFA and then using a Conditional Access policy to enforce it for the Azure SQL resource (Configure and manage Microsoft Entra authentication with Azure SQL[14]); Conditional Access is the Microsoft Entra control that sets requirements, such as compliant devices or specific authentication methods, for a connection to succeed (Microsoft Entra authentication for Azure SQL[6]). Microsoft Entra Password authentication, the SqlClient Active Directory Password keyword, uses the resource owner password credentials flow, which can't satisfy MFA requirements, so it fails for exactly these users (Microsoft Entra authentication for Azure SQL[6]; Connect to Azure SQL with Microsoft Entra authentication and SqlClient[1]).

Active Directory Integrated looks like Windows authentication but isn't. It acquires a Microsoft Entra token by using the signed-in domain identity, whereas Integrated Security=true uses Windows authentication directly with SQL Server (Connect to Azure SQL with Microsoft Entra authentication and SqlClient[1]). The Managed Instance feature that keeps true Windows authentication working is covered in Windows authentication on Azure SQL Managed Instance below.

Unattended workloads

Managed identity authentication is token-based: the identity is assigned to the resource that wants to authenticate, and the Azure identity platform validates that relationship, so the application stores no password (Microsoft Entra authentication for Azure SQL[6]). Microsoft prefers managed identities for applications in Azure for that reason. A service principal is the other workload identity, and authenticating it with an application (client) secret isn't recommended because a password can be guessed or leaked; the secret is a credential you must protect and rotate (Microsoft Entra authentication for Azure SQL[6]).

Pick the method by the caller: an interactive method for people, preferably a managed identity for Azure workloads, or a service principal for a workload.

Microsoft Entra-only authentication

Microsoft Entra-only authentication is a setting on an Azure SQL Database logical server or an Azure SQL Managed Instance that makes it accept Microsoft Entra authentication only (Microsoft Entra-only authentication with Azure SQL[16]). It removes methods rather than adding one, which is why it needs planning.

Enabling it disables SQL authentication at the server or managed instance level, including connections from SQL server administrators, logins and users, for the server and every database on it (Microsoft Entra-only authentication with Azure SQL[16]). On Managed Instance it also disables Windows Authentication for Microsoft Entra principals (Microsoft Entra authentication for Azure SQL[6]). It doesn't remove existing SQL authentication logins and users; it only prevents them from connecting. Microsoft Entra accounts with the right permissions can even create new SQL logins and users while it's on, and those new accounts can't connect either. The service also generates a server administrator name during provisioning, which can appear in the Azure portal even when SQL authentication is disabled, so a visible admin name is not evidence that SQL authentication still works.

Who can switch it is deliberately narrow. Highly privileged roles such as subscription Owner and Contributor, and the SQL Security Manager role, can enable or disable it, while the SQL Server Contributor and SQL Managed Instance Contributor roles can't, in line with separation of duties (Microsoft Entra-only authentication with Azure SQL[16]). It can be set in the Azure portal, Azure CLI, PowerShell or REST API, during server creation with an Azure Resource Manager (ARM) template, and Azure Policy can require new servers to be created with it enabled.

Before you enable it, find every application that still connects with SQL authentication and move it to a Microsoft Entra method. After the rollout, an application that fails because it sends a SQL user name and password needs a different authentication method: rotating its SQL password changes nothing, because the server refuses SQL authentication itself.

The setting therefore blocks these methods without deleting accounts, and only a few roles can switch it. Move every application that still uses SQL authentication to a Microsoft Entra method before you enable it, because once it is on, SQL authentication users can't connect to the server or any of its databases (Microsoft Entra-only authentication with Azure SQL[16]).

Microsoft Entra authentication for SQL Server

SQL Server, unlike Azure SQL Database and Managed Instance, needs a host integration before it can use Microsoft Entra ID, and this section covers which integration applies where. SQL Server 2022 and later supports Microsoft Entra authentication on Windows and Linux on-premises and on Azure Windows VMs, and the existing SQL and Windows authentication modes remain unchanged (Microsoft Entra authentication for SQL Server[17]). It works with standalone instances and Always On availability groups; SQL Server failover cluster instances don't currently support Microsoft Entra authentication.

Three integration paths

Choose the path by where the instance runs (Microsoft Entra authentication for SQL Server[17]):

Where SQL Server runs Integration path
An Azure VM Register the VM with the SQL IaaS Agent extension; no Azure Arc registration is needed
An on-premises Windows or Linux host Register with Azure Arc by installing the Azure Arc agent (the Azure Connected Machine agent) and the Azure extension for SQL Server
Windows, without Azure Arc Configure certificates, registry settings and Microsoft Entra application registrations manually

SQL Server on Azure VMs: the managed identity

The Azure VM path has three prerequisites: SQL Server 2022 or later, registration with the SQL IaaS Agent extension, and an existing system-assigned or user-assigned managed identity in the same Microsoft Entra tenant as the VM (Enable Microsoft Entra authentication for SQL Server on Azure VMs[15]). A system-assigned identity shares the VM's lifetime, so it needs no separate cleanup, while a user-assigned identity can be attached to several SQL Server VMs.

The identity exists for one job. When you create a Microsoft Entra login and when a user connects with it, SQL Server uses this managed identity to query Microsoft Graph, the API through which SQL retrieves and validates identity information from Microsoft Entra ID (Microsoft Entra authentication for Azure SQL[6]), so it needs three Microsoft Graph application permissions, User.Read.All, GroupMember.Read.All and Application.Read.All, or membership in the Microsoft Entra Directory Readers role (Enable Microsoft Entra authentication for SQL Server on Azure VMs[15]). Changing Directory Readers role assignments requires Privileged Role Administrator rights, and assigning the role to a group lets the group's owners add the VM identity without involving a role administrator each time.

A directory lookup failure while creating or authenticating a Microsoft Entra login on the VM therefore points at that managed identity's directory permissions. Adding the connecting application's database user to db_owner changes database authorization and leaves the lookup failing.

The host decides the integration: SQL IaaS Agent on Azure VMs, Azure Arc on other hosts, and a manual setup on Windows when Arc isn't wanted, always with an instance topology other than a failover cluster instance.

Authentication modes on SQL Server

SQL Server still offers its two original authentication modes, and this section covers how the mode decides whether SQL logins are accepted.

During installation, the Database Engine is set to either Windows Authentication mode or SQL Server and Windows Authentication mode (mixed mode), and you can change the mode at any time afterward (Change server authentication mode[18]). Windows Authentication mode rejects SQL-authenticated logins; mixed mode accepts both Windows and SQL authentication. To change it in SQL Server Management Studio (SSMS), right-click the server in Object Explorer, select Properties, select the new mode under Server authentication on the Security page, and then restart the Database Engine; if SQL Server Agent is running, restart it too. Changing the mode through T-SQL requires sysadmin membership or the CONTROL SERVER permission.

The sa login doesn't follow the mode change. If Windows Authentication mode was chosen at installation, sa is disabled, and switching to mixed mode later leaves it disabled (Change server authentication mode[18]). Microsoft advises against enabling sa unless an application requires it, because it's a well-known account that attackers target. Give each application that needs SQL authentication its own SQL login instead.

A client that sends a SQL user name and password keeps using SQL authentication whatever logins exist on the server. Creating a Windows login with the same name doesn't change the protocol that client uses, so a "login failed" for that client on a Windows-only server is fixed by mixed mode plus a SQL login.

On SQL Server, SQL logins need mixed mode; restart the engine after changing the mode.

Windows authentication on Azure SQL Managed Instance

Managed Instance offers Windows authentication on top of Microsoft Entra ID, and this section covers the feature that provides it.

Azure SQL Database doesn't support Windows authentication, while Managed Instance supports it as Windows Authentication for Microsoft Entra principals (Features comparison: Azure SQL Database and Azure SQL Managed Instance[19]). Kerberos authentication for Microsoft Entra ID (Microsoft Entra Kerberos) is what enables Windows Authentication access to the managed instance (Windows Authentication for Microsoft Entra principals on Azure SQL Managed Instance[20]). It exists for legacy applications that can't change their authentication: the code is no longer available, they depend on legacy drivers, or the clients can't be changed. It works from devices or VMs joined to Active Directory, to Microsoft Entra ID, or hybrid joined, and it requires neither new on-premises infrastructure nor a Microsoft Entra Domain Services deployment.

This differs from moving the application to a Microsoft Entra method such as Active Directory Integrated (Choosing a Microsoft Entra authentication method above): Windows Authentication for Microsoft Entra principals keeps the application's existing Windows authentication stack unchanged. Replacing an unchanged Integrated Security connection with a SQL login on the instance provisions a different credential type and doesn't satisfy the client's Windows authentication flow.

On Managed Instance, an application that must keep Windows authentication uses Windows Authentication for Microsoft Entra principals, which stops working once Microsoft Entra-only authentication is enabled (Microsoft Entra-only authentication above).

Creating logins and mapped database users

On SQL Server and Managed Instance, the usual provisioning path is a login at server level followed by a mapped user in each database; this section covers those statements, their options, and who may run them.

Provisioning on SQL Server: a SQL login, a Windows login and a mapped user

-- SQL authentication login: SQL stores and checks the password
CREATE LOGIN [AppLogin] WITH PASSWORD = '<strong-password>',
    DEFAULT_DATABASE = [AppDb];

-- Windows login: maps an existing Windows account; SQL stores no password
CREATE LOGIN [CONTOSO\ReportUser] FROM WINDOWS;

-- Run in AppDb: the database identity that roles and permissions attach to
CREATE USER [AppUser] FOR LOGIN [AppLogin] WITH DEFAULT_SCHEMA = [Reporting];

The first statement provisions a SQL-password login, and the second maps an existing Windows principal rather than storing its domain password in SQL (CREATE LOGIN (Transact-SQL)[21]). DEFAULT_DATABASE in the first statement selects the database the login starts in when the client doesn't name one; if the option is omitted, the default database is master.

Password policy options

These options apply to SQL Server logins that use SQL authentication (CREATE LOGIN (Transact-SQL)[21]):

Option What it does Default
CHECK_POLICY Enforces the Windows password policies of the computer running SQL Server ON
CHECK_EXPIRATION Enforces password expiration OFF
MUST_CHANGE (after the password) Forces a password change at first connection Not set

Two combinations fail: MUST_CHANGE requires both CHECK_EXPIRATION and CHECK_POLICY set to ON, and CHECK_POLICY = OFF with CHECK_EXPIRATION = ON isn't supported (CREATE LOGIN (Transact-SQL)[21]). Azure SQL Database only enforces password complexity for its password policy (Authorize database access[2]), so don't assume SQL Server's expiration and lockout behavior there.

Default schema is name resolution, not access

DEFAULT_SCHEMA in the last statement of the listing specifies the first schema searched when the server resolves object names for this user (CREATE USER (Transact-SQL)[11]). It grants nothing on that schema. When an unqualified name doesn't resolve, fix the default schema or qualify the name; when the name resolves but the statement is refused, grant the missing permission.

Who may create logins and users

The required permission depends on the version and on the scope of the statement (CREATE LOGIN (Transact-SQL)[21]; CREATE USER (Transact-SQL)[11]):

Statement SQL Server 2022 and later Earlier versions
CREATE LOGIN CREATE LOGIN permission on the server, or membership in ##MS_LoginManager##, one of the fixed server roles introduced in SQL Server 2022 (Server-level roles[22]); Azure SQL Database's corresponding roles are in Server-level roles ALTER ANY LOGIN, or securityadmin membership
CREATE USER CREATE USER permission on the database ALTER ANY USER on the database

On SQL Server 2022 and later, a creation-only delegate gets GRANT CREATE LOGIN TO [ProvisionLogin]; at server scope and GRANT CREATE USER TO [ProvisionUser]; in each database where it must create users. securityadmin would also work for login creation but adds broader authority to manage existing logins than a creation-only requirement allows. When provisioning fails, check the permission at the scope of the failing statement: authority to create users in a database doesn't include creating logins on the server.

A login carries the credential and its password policy, the mapped user carries the database identity and default schema, and each needs its own creation permission at its own scope.

Contained database users

A contained database user authenticates in its own database, with no login in master, and this section covers when that independence is worth choosing. The typical case is a tenant database that must move between servers on its own: a login-mapped user would depend on a login that may not exist on the destination.

A contained SQL user, created in the tenant database

CREATE USER [TenantUser] WITH PASSWORD = '<strong-password>';

To connect as a contained user, the connection string must always name the user database, because the engine uses that parameter to know which database manages the authentication (Make your database portable by using contained databases[23]). Azure SQL Database always requires the database name in the connection string and supports contained users directly. SQL Server needs two settings first: the contained database authentication server option enabled with sp_configure, and the database set to partial containment. Azure SQL Managed Instance behaves like SQL Server for contained databases.

The trade-off is about where passwords live (Authorize database access[2]). With a login and mapped users, one password serves every database the user can access, but geo-replication adds work because the login must be created on the primary and every secondary server. With contained users, the authentication information is stored in each database and replicates automatically to geo-replicated databases, but the same person with contained SQL users in several databases has a separately managed password in each.

Because a contained user isn't associated with a login, it can't become orphaned when the database moves, that is, left with no login on the server that has its SID (Troubleshooting authentication and authorization below covers detection and repair) (Troubleshoot orphaned users[12]). Creating the tenant's account only in master to simplify moves does the opposite: the tenant database stays dependent on a principal outside it.

Choose contained users when the database must carry its own authentication, and login-mapped users when one credential must serve many databases on the same server.

Creating users from Microsoft Entra identities

This section covers the T-SQL that turns a Microsoft Entra identity into a database principal on Azure SQL Database and Managed Instance. A Microsoft Entra identity can become a principal in three forms: a login (server principal), a login-based user, or a contained database user (Microsoft Entra authentication for Azure SQL[6]).

The three Microsoft Entra principal forms

-- Contained Microsoft Entra user, created in the user database
CREATE USER [adrian@contoso.com] FROM EXTERNAL PROVIDER;

-- Login, created in master ...
CREATE LOGIN [TeamLeads] FROM EXTERNAL PROVIDER;
-- ... then a login-based user, created in the user database
CREATE USER [TeamLeads] FROM LOGIN [TeamLeads];

FROM EXTERNAL PROVIDER tells the engine the principal is a Microsoft Entra identity, and the engine validates the name in Microsoft Entra ID (CREATE USER (Transact-SQL)[11]). The caller must itself be signed in with a Microsoft Entra account: to create contained users mapped to Microsoft Entra identities you must be logged in with a Microsoft Entra account (Authorize database access[2]), and the SQL server administrator can't create Microsoft Entra logins or users in any database (Microsoft Entra server principals[24]). An individual user is named by user principal name, as in the listing, while groups, service principals and managed identities are named by display name (Microsoft Entra logins and users with nonunique display names[25]).

Group users

Efficient access management assigns permissions to Microsoft Entra security groups and roles instead of to individuals: create a contained database user for the group and add it to a built-in or custom role (Authorize database access[2]), for example CREATE USER [TeamReaders] FROM EXTERNAL PROVIDER; followed by ALTER ROLE db_datareader ADD MEMBER [TeamReaders]; when every user table and view must be readable. Onboarding and removal then happen through group membership, with no per-person SQL grant. When the group needs only some objects, give it a custom role instead (Fixed and custom database roles below). Provisioning the group only as a user in master creates no principal in the application database, so it has no role membership there. One limit to know: Microsoft Entra users and service principals that are members of more than 2048 Microsoft Entra security groups aren't supported and can't log in to the database (Microsoft Entra authentication for Azure SQL[6]).

Same name, different principal

A contained Microsoft Entra user and a Microsoft Entra login with the same name are not connected: the database user doesn't inherit permissions from the server login, and identities can become conflated in connection requests (Microsoft Entra authentication for Azure SQL[6]). SSMS makes the mistake easy, because its scripting command for a new user always generates the contained form without checking for a same-named login in master (Microsoft Entra server principals[24]). Renaming the contained user to match the login changes nothing; when the database identity must carry the login's server-level permissions, use the login-based form from the listing.

The SID column of sys.database_principals tells the two apart: a contained user's SID is a binary string of length 16, while a login-based user's SID is length 18 with an AADE suffix (Microsoft Entra server principals[24]). Remove that suffix before correlating the user with its login in sys.server_principals; unequal raw SIDs are expected, not proof of an orphaned user. The query below, from Microsoft's documentation, classifies each Microsoft Entra principal in a database (Microsoft Entra authentication for Azure SQL[6]).

Classify Microsoft Entra database principals as contained or login-based

SELECT CASE
    WHEN CONVERT(VARCHAR(100), sid, 2) LIKE '%AADE' AND len(sid) = 18 THEN 'login-based user'
    ELSE 'contained database user'
    END AS user_type,
    *
FROM sys.database_principals WHERE TYPE = 'E' OR TYPE = 'X';

In that query, TYPE = 'E' selects users and applications and TYPE = 'X' selects groups.

Nonunique names: WITH OBJECT_ID, or SID and TYPE

Name-based creation works for a unique name. When a group or application display name isn't unique, add WITH OBJECT_ID and a distinguishing suffix that becomes the SQL alias (Microsoft Entra logins and users with nonunique display names[25]):

CREATE USER [myapp_suffix] FROM EXTERNAL PROVIDER
    WITH OBJECT_ID = '<service-principal-object-id>';

For an application, take the object ID from Enterprise applications in the SQL resource's tenant, because the object ID shown on the App registrations overview page is a different value (Microsoft Entra logins and users with nonunique display names[25]): the app registration is the application's definition, and the enterprise application is its service principal in your tenant, the object SQL needs (What is application management in Microsoft Entra ID?[26]). The WITH OBJECT_ID extension is supported for SQL Server 2025 and later versions (Microsoft Entra logins and users with nonunique display names[25]).

Azure SQL Database also accepts a route that skips the directory lookup: CREATE USER [IdentityAlias] WITH SID = <binary-identity-id>, TYPE = E; for a user or service principal (a managed identity included) or TYPE = X for a group (CREATE USER (Transact-SQL)[11]). The SID is the object ID of a user or group, or the application (client) ID of a service principal or managed identity, converted to binary(16). The engine doesn't validate that ID in Microsoft Entra ID, so you must verify it yourself.

Create Microsoft Entra users while signed in with Microsoft Entra, name them unambiguously, and choose contained or login-based deliberately, because a matching name never links the two.

Directory lookups for automated user creation

When a pipeline rather than a person creates Microsoft Entra users, a second identity enters the picture, and this section covers which identity needs which permission. To create a Microsoft Entra principal, Azure SQL calls Microsoft Graph to retrieve information about the identity and validate that it exists (Microsoft Entra authentication for Azure SQL[6]).

Three parties are involved: the caller that runs CREATE USER ... FROM EXTERNAL PROVIDER, the Azure SQL engine, and Microsoft Graph. When the caller is a user identity, no extra permission is needed for the Graph query (Microsoft Entra authentication for Azure SQL[6]). When the caller is a service principal or managed identity, the lookup can't run as the caller, because an application can't impersonate another application; the engine instead uses its server identity, the primary managed identity assigned to the logical server or managed instance (Microsoft Entra service principals with Azure SQL[27]). So a statement that succeeds for an administrator signed in interactively can still fail under automation. The figure below shows who needs what.

The two permissions are independent. The caller needs SQL permission to create the user: in Azure SQL that is ALTER ANY USER in the database (Microsoft Entra authentication for Azure SQL[6]), whereas the SQL Server rule, which depends on the version, is in Creating logins and mapped database users. The server identity needs Microsoft Graph read access:

What is being created Minimum Graph permission for the server identity
A user or login for a service principal or managed identity Application.Read.All
A user or login for a Microsoft Entra user User.Read.All
A user or login for a Microsoft Entra group GroupMember.Read.All
Microsoft Entra authentication on Managed Instance Directory Readers role for the managed instance identity

The table comes from Microsoft Entra authentication for Azure SQL[6]. Directory Readers is the smallest role that covers everything Azure SQL needs, but Microsoft recommends the individual Graph permissions over Directory Readers for least privilege, and assigning either requires the Privileged Role Administrator role (Microsoft Entra service principals with Azure SQL[27]). The same group-assignment shortcut described for the VM identity in Microsoft Entra authentication for SQL Server above works for server identities. Granting Graph permissions only to the deployment application's own identity misses the point: the engine performs the lookup as the server identity, so that is where the permission must be.

If the statement fails with SQL error 33134, read the Microsoft Entra message inside it (Configure and manage Microsoft Entra authentication with Azure SQL[14]). Access denied, or a demand to enroll in MFA, usually means a Conditional Access policy is blocking access to the external provider, and allowing access to application 00000003-0000-0000-c000-000000000000 (the Microsoft Graph API) resolves it. A message that access between first-party applications must be handled through preauthorization means the caller is signed in as a service principal, and the command succeeds when a user runs it. Neither case is fixed by adding the caller to broader SQL roles.

Automated creation of Microsoft Entra users needs both a SQL permission for the caller and a Graph permission for the server identity, and a Conditional Access block is a third, separate cause.

Deployment pipeline(service principal)Needs ALTERANY USERCREATE USERAzure SQL engine(server or instance)server identityMicrosoft Graphdirectory lookupServer identityneeds Graphread permissions
Who needs which permission when a service principal creates a Microsoft Entra user: SQL permission for the caller, Graph read for the server identity

Microsoft Entra logins in Azure SQL Database

Azure SQL Database also supports Microsoft Entra logins in the virtual master database, the master database of the logical server, and this section covers how they are bootstrapped, where groups fall short, and how disabling and caching behave (Microsoft Entra server principals[24]). Logins add what contained users can't have: membership in Azure SQL Database server roles and login-level control.

Bootstrapping

The first Microsoft Entra login can only be created by the Microsoft Entra administrator; after that, members of the loginmanager role can create them too, and the creator must belong to the same Microsoft Entra directory as the database (Microsoft Entra server principals[24]). loginmanager is a database role in virtual master whose members can create and delete logins (Database-level roles[13]), the older counterpart of the ##MS_LoginManager## server role in the listing below; Server-level roles covers both. The SQL server administrator can't create Microsoft Entra logins or users in any database. New Microsoft Entra logins in master receive VIEW ANY DATABASE by default.

Run in virtual master while connected as the Microsoft Entra administrator

CREATE LOGIN [ops-lead@contoso.com] FROM EXTERNAL PROVIDER;
ALTER SERVER ROLE [##MS_LoginManager##] ADD MEMBER [ops-lead@contoso.com];

The second statement delegates login management, because Azure SQL Database server roles can be assigned to logins in virtual master (Microsoft Entra server principals[24]).

Where group logins fall short

A Microsoft Entra group login has limits that an individual login doesn't (Microsoft Entra server principals[24]). Azure SQL Database server roles aren't supported for group logins, and a database's ownership can't be changed to a group. Members of a group login aren't individually in sys.server_principals, and their individual logins aren't implicitly created, so they have no default schema and can't run operations such as CREATE SCHEMA until an individual login is created for the person or a default schema is assigned to the group. Use an individual or application login when a fixed server role or database ownership is required.

The Microsoft Entra administrator also overrides logins. If an account already has access as the Microsoft Entra administrator, individually or through a group, a login created for it has no effect: T-SQL doesn't block creating it, but the account authenticates with the administrator's effective permissions (Microsoft Entra server principals[24]).

Disabling logins and refreshing caches

ALTER LOGIN [IdentityName] DISABLE stops connections through that login and through all database users created from it (Microsoft Entra server principals[24]). It doesn't affect contained database users, because they aren't associated with logins, so disabling a login doesn't revoke access the same identity has through an independent contained user. It isn't supported for Microsoft Entra groups, and it can't disable an individual who belongs to a group login in master, such as a Microsoft Entra administrator group.

Changes to a Microsoft Entra login's permissions and server role memberships take effect, by default, the next time the login connects; open connections keep their old rights (Microsoft Entra server principals[24]). To force a change to take effect, clear the authentication cache and the TokenAndPermUserStore cache, then have the login reconnect:

DBCC FLUSHAUTHCACHE;
DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS;

Microsoft Entra logins give Azure SQL Database server-role delegation, but groups can't use it and the administrator identity always wins. Disabling blocks subsequent connections through the login's own path; existing sessions must reconnect for server role membership changes to apply, even after a cache flush.

How GRANT, DENY and REVOKE combine

Authorization is written with three statements, and the engine combines them with one documented algorithm; this section covers that model before the sections on roles and securables rely on it.

Statement Effect
GRANT Gives a principal a permission on a securable
DENY Denies the permission and prevents the principal from inheriting it through group or role memberships (DENY (Transact-SQL)[28])
REVOKE Removes a previously granted or denied permission (REVOKE (Transact-SQL)[29])

The permission check

The engine evaluates three things (Permissions (Database Engine)[4]). The security context is the set of principals that contribute permissions: the login, the user, role memberships, Windows group memberships, and, while a signed module runs, the principal for its signing certificate. The permission space is the securable plus every securable class that contains it, so a table's access is affected by table, schema, database and server permissions. The required permission is what the statement needs, such as SELECT or EXECUTE.

The figure below simplifies Microsoft's general steps. The check is bypassed for a member of the sysadmin fixed server role or the dbo user of the current database. Access is allowed when ownership chaining applies and the check on the object earlier in the chain passed; ownership chaining applies when, for example, a procedure and the table it reads have the same owner (Metadata visibility configuration[30]). Otherwise the engine collects every GRANT and DENY, explicit or implied by a covering permission, for the security context across the permission space. The check fails if the required permission is denied to any identity in the security context, which is what "denied anywhere" means in the figure, and passes if it is granted to any of them, directly or implicitly (Permissions (Database Engine)[4]).

Consequences that questions test

  • DENY outranks GRANT, and a second, direct GRANT doesn't override a DENY inherited from a role. The exceptions: DENY doesn't apply to object owners or sysadmin members (DENY (Transact-SQL)[28]), and a column-level GRANT overrides a table-level DENY until a later table-level DENY removes the column grant (Permissions (Database Engine)[4]).
  • REVOKE is not DENY. Revoking a direct grant removes only that grant; the principal keeps access that arrives through a role, so review every principal in the security context before deciding access is gone.
  • Some principals can't be targeted at all: you can't grant, deny or revoke permissions to sa, dbo, the entity owner, information_schema, sys, or yourself (Permissions (Database Engine)[4]).

The SQL model differs from Azure RBAC, which you meet when assigning Azure roles to the same resources. Azure RBAC is additive, its effective permissions are the union of all role assignments, and, for generally available scenarios, the deny assignments that subtract from it are created and managed by Azure (Azure deny assignments[31]). In SQL, any principal holding CONTROL at a scope, such as a db_owner member at database scope, can issue a DENY there (DENY (Transact-SQL)[28]), and that denial wins over grants.

In the ordinary permission check, subject to the exceptions above, a permission works when no blocking DENY applies in the caller's security context and something grants it; REVOKE merely deletes one entry from that collection.

sysadmin memberor the dbo user?CheckbypassedYesNoOwnership chainapplies, earliercheck passed?AccessallowedYesNoCollect GRANT andDENY for the contextRequired permissiondenied anywhere?AccessdeniedYesNoRequired permissiongranted anywhere?AccessallowedYesNoAccess denied:no GRANT found
SQL permission check, simplified from Microsoft's general steps: bypass, ownership chain, any DENY fails, then any GRANT passes

public and guest: baseline and fallback access

Two built-in principals affect access without individual grants, and this section covers how they differ: public is a role every user belongs to, and guest, when enabled, is a user that stands in for logins with no user of their own.

The public role

Every login belongs to the public fixed server role, and every database user belongs to the public database role (Principals (Database Engine)[3]). A user that hasn't been granted or denied a specific permission on a securable inherits whatever is granted to public on it. The public roles can't be dropped and nobody can leave them, although you can revoke permissions from them, and many default public permissions are needed for routine operations.

That makes public the wrong place for business access. A grant to public to serve one department's reports reaches every current and future user of the database; grant application access to a dedicated role instead (Fixed and custom database roles below). It also cuts the other way: because the permission check includes each user's public membership (How GRANT, DENY and REVOKE combine above), a DENY to public blocks users who received the same permission through other roles. When users have baseline access nobody remembers granting, review public.

The guest user

Each database includes a guest user, and its permissions are inherited by users who have access to the database but no user account in it (Principals (Database Engine)[3]). It can't be dropped; it's disabled by revoking its CONNECT permission (REVOKE CONNECT FROM GUEST;), which works in any database other than master or tempdb. On SQL Server, enabling it is GRANT CONNECT TO guest, not CREATE USER, because the user already exists. Review this fallback path separately from public and from explicitly mapped users.

The name collides with Microsoft Entra ID's guest users. A Microsoft Entra guest is an external person invited through B2B collaboration, and such guests connect as individual Microsoft Entra users or through group membership (Microsoft Entra authentication for Azure SQL[6]); the SQL guest user is an unrelated database principal.

public gives every user a baseline, guest, when enabled, gives users without a database identity a fallback one, and application-specific business permissions belong in dedicated roles instead.

Fixed and custom database roles

Database roles group database principals so that permissions are managed once per role, and this section covers the predefined fixed roles and the custom roles you build. Database-level roles are database-wide in their permission scope, and there are two types: fixed database roles, predefined in every database, and user-defined (custom) roles (Database-level roles[13]). The permissions of fixed roles can't be changed, except for public, the role every user belongs to, which public and guest above covers and the table below leaves out; custom roles get their permissions through GRANT, DENY and REVOKE. Membership is managed with ALTER ROLE ... ADD MEMBER and ALTER ROLE ... DROP MEMBER, so a membership change never requires recreating the user.

The nine fixed database roles other than public

Role What members can do (Database-level roles[13]) Least-privilege note
db_owner All configuration and maintenance on the database, except maintenance requiring server-level permissions in Azure SQL Database Grant it to only a few users (Authorize database access[2])
db_securityadmin Modify membership of custom roles only, and manage permissions Can elevate its own privileges; monitor it
db_accessadmin Add or remove database access for Windows logins, Windows groups and SQL Server logins Manages who gets in, not role membership
db_backupoperator Back up the database Not applicable in Azure SQL Database, where backup and restore T-SQL isn't available (Managed Instance excepted)
db_ddladmin Run any Data Definition Language (DDL) command in the database Can elevate privileges by changing code that runs with high privileges
db_datawriter Add, delete or change data in all user tables Usually combined with db_datareader
db_datareader Read all data from all user tables and views More than a selected-table need requires
db_denydatawriter Can't add, modify or delete data in any user table Database-wide denial
db_denydatareader Can't read data from any user table or view, or the database's metadata Database-wide denial

Microsoft notes that fixed roles other than db_owner help a simple development database get going quickly but aren't recommended for most production databases; db_datareader, for example, grants read access to every table (Authorize database access[2]).

Three useful distinctions follow. Reading and writing are independent: a reporting account needs only db_datareader, while a workload that reads and modifies rows usually needs both db_datareader and db_datawriter, and an application required only to read and modify rows does not need db_ddladmin, which delegates schema definition instead. The denial roles are blunt: they deny across every user table, so when a role must keep ordinary access to some objects, use narrowly scoped explicit permissions instead. Security administration has two shapes: db_securityadmin manages custom-role membership and permissions but not fixed-role membership, which db_owner members manage, while db_accessadmin controls database access (Database-level roles[13]). When someone must manage one custom role only, GRANT ALTER ON ROLE::[ApplicationRole] TO [RoleManager]; is narrower than any database-wide security role, because ALTER on the role is enough to change its membership, while changing a fixed role's membership additionally needs db_owner (ALTER ROLE (Transact-SQL)[32]). Microsoft also advises against adding custom roles as members of fixed roles, which can enable unintended privilege escalation.

Building a custom role

A custom role needs three separate statements, as in the listing below.

A read-only reporting role for one table

-- 1. The role itself, owned by dbo; it starts with no permissions and no members
CREATE ROLE [ReportRole] AUTHORIZATION [dbo];
-- 2. Its permissions
GRANT SELECT ON OBJECT::Reporting.MonthlyTotals TO [ReportRole];
-- 3. Its members
ALTER ROLE [ReportRole] ADD MEMBER [ReportUser];

AUTHORIZATION in step 1 names the database user or role that owns the new role, and the owner can add or remove its members; creating a role requires CREATE ROLE permission or db_securityadmin membership (CREATE ROLE (Transact-SQL)[33]). When a user belongs to several roles, its permissions are the aggregate of them all (Authorize database access[2]).

Fixed database roles are fast but database-wide; when the requirement names specific objects or one role to administer, build a custom role or grant the single permission instead.

Server-level roles

Server-level roles group logins, and what you can do with them differs by platform; this section covers custom server roles on SQL Server and Managed Instance and the fixed server roles of Azure SQL Database.

Custom server roles on SQL Server and Managed Instance

CREATE SERVER ROLE applies to SQL Server and Azure SQL Managed Instance; after creating the role you configure its server-level permissions with GRANT, DENY and REVOKE, and add logins with ALTER SERVER ROLE (CREATE SERVER ROLE (Transact-SQL)[34]). Server roles can't be granted permissions on database-level securables.

A diagnostics server role (SQL Server or Managed Instance)

CREATE SERVER ROLE [DiagnosticRole];
GRANT VIEW SERVER STATE TO [DiagnosticRole];
ALTER SERVER ROLE [DiagnosticRole] ADD MEMBER [OperatorLogin];

Fixed server roles in Azure SQL Database

In Azure SQL Database the server is a logical concept and permissions can't be granted at server level, so Azure SQL Database provides fixed server-level roles instead, named with a ##MS_ prefix and ## suffix (Azure SQL Database server roles for permission management[5]). They correspond to the fixed server roles introduced in SQL Server 2022, such as the ##MS_LoginManager## option in Creating logins and mapped database users (Server-level roles[22]); the table below lists the Azure SQL Database set. Azure SQL Database doesn't support user-defined server roles (CREATE SERVER ROLE (Transact-SQL)[34]). The permissions of Azure SQL Database's fixed server roles can't be changed, and logins are added to them with ALTER SERVER ROLE ... ADD MEMBER (Azure SQL Database server roles[5]). A server role's permissions propagate to a database only where the login can connect: it needs a user in that database or membership in ##MS_DatabaseConnector##, and that applies to virtual master too.

Fixed server role What it delegates (Azure SQL Database server roles[5])
##MS_DatabaseConnector## CONNECT to any database without a user account there; it grants no SELECT or other data permission
##MS_DatabaseManager## Create and delete databases; the creator owns each database it creates and enters it as dbo, without gaining access to databases it doesn't own
##MS_LoginManager## Create and delete logins
##MS_ServerStateReader## VIEW SERVER STATE and related state reads; VIEW DATABASE STATE where the login has a user
##MS_ServerStateManager## Everything in ##MS_ServerStateReader## plus ALTER SERVER STATE, which allows operations such as DBCC FREEPROCCACHE
##MS_DefinitionReader## VIEW ANY DEFINITION and related catalog reads
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION plus VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION, which exposes password hashes of SQL authentication logins

Each pair separates a safe capability from a sensitive one. A read-only collector of dynamic management views (DMVs) belongs in ##MS_ServerStateReader##, because ##MS_ServerStateManager## also allows state changes such as clearing caches. ##MS_SecurityDefinitionReader## isn't a harmless subset of definition reading; Microsoft says to assign it only to trusted administrators and security personnel who need that access. A person who must create databases needs ##MS_DatabaseManager##; db_owner in one application database grants authority at the wrong scope.

The older master database roles

Before these server roles, Azure SQL Database delegated the same work through the dbmanager and loginmanager database roles in master, which take database users in master as members; Microsoft recommends ##MS_DatabaseManager## and ##MS_LoginManager## instead (Azure SQL Database server roles[5]). The membership statement differs accordingly: ALTER SERVER ROLE [##MS_LoginManager##] ADD MEMBER [ProvisionLogin]; targets a login. When SQL-authenticated database creation still goes through dbmanager, the member should be a user mapped to a login rather than a contained user with a password in master. Microsoft Entra group logins can't join any of these server roles (Microsoft Entra logins in Azure SQL Database above).

Server-level delegation uses custom server roles on SQL Server and Managed Instance and only fixed server roles on Azure SQL Database, and in both cases the narrowest role that covers the task is the right one.

Granting permissions in SSMS

SQL Server Management Studio (SSMS) configures the same permissions as T-SQL through the securable's own properties, and this section covers where those dialogs live and what their choices mean. The rule behind every dialog: the permission lives on the securable you open, granted to the principal you select.

For an object, the documented path is (Grant a permission to a principal[35]):

  1. In Object Explorer, expand the database that contains the object.
  2. Expand the folder that holds it, for example Programmability and then Stored Procedures.
  3. Right-click the object and select Properties.
  4. Select the Permissions page, add the users or roles, and specify the permissions each one has.
  5. Select OK.

The same steps work for tables, views, functions, assemblies and other securables (Grant a permission to a principal[35]). For table-specific SELECT or UPDATE, open the table; for a database-scoped permission, open the database's Properties and its Permissions page. A login's own properties configure server-level settings such as server role membership, so they are the wrong dialog for a permission on one object in one database.

Grant gives the principal the permission, With Grant also lets it grant that permission to others, and Deny explicitly refuses it, with the precedence described in How GRANT, DENY and REVOKE combine above. Permission rows display Grant, With Grant and Deny states, but not all options are available for every permission; With Grant is read-only and must be applied with the GRANT T-SQL statement. In the dialog, therefore, only the Grant and Deny check boxes are selected or cleared, and With Grant shows whether the delegation exists (Permissions or Securables page[36]). Delegating needs authority: the grantor must hold the permission with GRANT OPTION, or a higher permission that implies it (Grant a permission to a principal[35]).

Microsoft's best practices apply in the dialog as much as in T-SQL (Grant a permission to a principal[35]): grant permissions to roles rather than to individual logins or users, so replacing a person means changing membership only, and group similar objects in a schema so one schema grant serves a whole function.

In SSMS, open the securable first, then choose the principal and the permission; the dialog you open decides the scope of what you grant.

Least privilege across schemas and objects

Least privilege means matching each permission to both the operation required and the securable scope required, and this section covers how the securable hierarchy makes that possible. Securables nest: the server contains databases, a database contains schemas, a schema contains objects such as tables, views and procedures, and a table contains columns, as the figure below shows. Many permissions are nested along that hierarchy; for example, UPDATE on a schema includes UPDATE on each table in it (Authorize database access[2]). Azure SQL Database has no grantable server-level permissions (Principals at server and database scope above), so its hierarchy effectively starts at the database.

Pick the level that matches the need

  • Schema: GRANT SELECT ON SCHEMA::Reporting TO [ReportRole]; covers the schema's current objects and the ones created there later (Permissions (Database Engine)[4]).
  • Object or column: a smaller entitlement uses object grants, or column grants such as GRANT SELECT ON OBJECT::Customer(CustomerName) TO UserJoe; (Permissions (Database Engine)[4]).
  • Database: SELECT at database scope also reaches unrelated schemas, so it oversteps a requirement bounded to one schema, even though it would cover future reporting tables.

Permissions that are broader than they look

CONTROL confers ownership-like capabilities: the grantee has all permissions on the securable, can grant them, and holds CONTROL on everything beneath it (Permissions (Database Engine)[4]). ALTER on a scope lets the grantee create, alter or drop the securables in that scope, so ALTER on a schema permits changing every object in it. Neither is a substitute for a read-only requirement.

Creating an object takes two permissions: the database-level CREATE permission for the object type and ALTER on the schema where it will live (Permissions (Database Engine)[4]). A deployment role limited to one schema therefore gets:

Permissions for a deployment role limited to the Reporting schema

GRANT CREATE TABLE TO [DeploymentRole];
GRANT ALTER ON SCHEMA::Reporting TO [DeploymentRole];

That is narrower than db_ddladmin, which allows DDL throughout the database (Fixed and custom database roles above). REFERENCES is another permission that isn't what it seems: it's needed on a table to create a FOREIGN KEY constraint that references it, and on an object to create a view or function WITH SCHEMABINDING that references it (Permissions (Database Engine)[4]), and it doesn't grant SELECT on the data.

Choose the lowest level of the hierarchy that covers the requirement, and the weakest verb that performs the operation.

ServerDatabaseSchemaObject: table, view, procedureColumnMany permissions at one levelextend to the securables nested below
The securable hierarchy from server to column; many permissions granted at one level extend to the securables nested below it

Metadata visibility

Whether a user can see that an object exists is controlled separately from whether it can read the object's data, and this section covers that separation. Metadata visibility is limited to securables the user owns or has been granted some permission on (Metadata visibility configuration[30]). A query against a catalog view can therefore return an empty result for a table that exists, system views can return only a subset of rows, and metadata functions return NULL for users who lack the required permission.

When an operator must inspect an object's definition without reading its data, grant VIEW DEFINITION on that object; it can also be granted at database or server level, and SQL Server 2022 and later add the narrower VIEW SECURITY DEFINITION and VIEW PERFORMANCE DEFINITION (Metadata visibility configuration[30]). Broaden it to database scope only when the operator must inspect every definition. Adding the operator to db_datareader would reveal the business data the requirement excludes.

Visibility of subcomponents such as indexes, check constraints and triggers follows permission on the parent: any permission on a table, even SELECT on a single column, lets the user see the metadata of the whole table (Metadata visibility configuration[30]).

The practical rule for troubleshooting: before concluding that a deployment omitted an object, run the metadata query in the intended user's security context (Troubleshooting authentication and authorization below shows how), because an administrator sees rows the user can't.

Stored procedures, ownership chains and signing

A stored procedure can expose one approved operation without exposing the tables behind it, and this section covers how that works: EXECUTE permission and ownership chaining, and, where the chain is broken, module signing or an EXECUTE AS clause to supply the missing rights temporarily.

The starting point is to grant EXECUTE on the procedure rather than direct permissions on the underlying tables. A procedure can require both EXECUTE on itself and permissions on the tables it references (Permissions (Database Engine)[4]), and ownership chaining is what usually removes the second requirement: when the procedure and the referenced table have the same owner, the chain is unbroken and the engine allows access to the table once the check on the procedure has passed (How GRANT, DENY and REVOKE combine above). When ownership chaining doesn't apply, the caller needs its own permission on the referenced objects, as Microsoft's signing example shows: a user with EXECUTE on a procedure, but no ownership chain and no SELECT on the table, can't read the table through the procedure (ADD SIGNATURE (Transact-SQL)[37]). An application that may change data only through validation procedures therefore gets EXECUTE, never db_datawriter, which would also allow changes outside the procedures.

Principals that hold rights but can't sign in

Some principals exist only to carry permissions. CREATE USER ... WITHOUT LOGIN creates a user that can't log in but can be granted permissions, and users created from a certificate or an asymmetric key can't log in either but can be granted permissions and can sign modules (CREATE USER (Transact-SQL)[11]). Logins created from certificates or asymmetric keys likewise serve code signing only and can't be used to connect (CREATE LOGIN (Transact-SQL)[21]).

Module signing

When ownership chaining doesn't apply, signing supplies the missing rights without changing the execution context or permanently elevating the caller. Create a certificate, create a user from it, grant that user the rights the procedure needs, and sign the procedure with ADD SIGNATURE TO [ProcedureName] BY CERTIFICATE [SigningCertificate]. While a signed module runs, its signatures are temporarily added to the caller's SQL token; they are lost when the module calls another module or finishes (ADD SIGNATURE (Transact-SQL)[37]). The signature is dropped when the module is changed, and Microsoft cautions that signing should only grant permissions, never deny or revoke them. The worked example shows the signing fragment for an existing procedure.

Changing the execution context with an EXECUTE AS clause is the other documented option, and Microsoft lists impersonation alongside module signing as ways to elevate permissions temporarily (Authorize database access[2]). Scope either one to the operation; making the caller a permanent member of an administrative role defeats the purpose.

Grant EXECUTE on the procedure, rely on an unbroken ownership chain where owners match, and sign the procedure where they don't.

Troubleshooting authentication and authorization

Diagnosis starts by deciding which check failed, authentication or authorization, and at which scope, and this section collects the symptoms from earlier sections and adds the two techniques that need their own explanation: repairing orphaned users and testing effective permissions.

Where to look first

Symptom Likely cause Covered in
Every Microsoft Entra connection fails at once The Microsoft Entra administrator was removed Microsoft Entra authentication for Azure SQL
SQL-authenticated applications fail after a security change Microsoft Entra-only authentication was enabled Microsoft Entra-only authentication
A SQL login fails on SQL Server The instance is in Windows Authentication mode Authentication modes on SQL Server
CREATE USER ... FROM EXTERNAL PROVIDER fails only under automation The server identity lacks Graph read access, or error 33134 reports a Conditional Access block Directory lookups for automated user creation
A correctly provisioned Microsoft Entra user can't log in The identity is in more than 2048 security groups Creating users from Microsoft Entra identities
A Microsoft Entra login's server-role membership change in Azure SQL Database doesn't take effect The login hasn't reconnected since the change; running DBCC FLUSHAUTHCACHE doesn't apply it to an open session without a reconnect (Azure SQL Database server roles[5]) Microsoft Entra logins in Azure SQL Database
An existing object is missing from catalog queries Metadata visibility Metadata visibility

Orphaned users

A user is orphaned when it's based on a login in master but no login with its SID exists there, which happens after a restore or attach on another server where the login was never created, or after the login is dropped; a re-created login gets a different SID, so a same-named login doesn't fix it (Troubleshoot orphaned users[12]). On SQL Server, this query lists the SQL authentication users in the current database that no login matches:

Find orphaned SQL authentication users (SQL Server, run in the user database)

SELECT dp.type_desc, dp.sid, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.sid = sp.sid
WHERE sp.sid IS NULL
    AND dp.authentication_type_desc = 'INSTANCE';

In Azure SQL Database, compare the SIDs from sys.sql_logins in master with the SIDs in the user database's sys.database_principals instead (Troubleshoot orphaned users[12]). Repair the mapping in one of two ways: ALTER USER [DatabaseUser] WITH LOGIN = [ExistingLogin]; maps the user to a login that already exists, or CREATE LOGIN ... WITH PASSWORD = '<password>', SID = <SID>; re-creates the missing login with the user's original SID. Both keep the existing user, and with it every role membership and permission it holds; dropping and recreating the user would discard them. The older sp_change_users_login procedure is deprecated.

Testing effective permissions

sys.fn_my_permissions(NULL, 'DATABASE') returns the effective permissions of the caller on the database, counting permissions granted directly, implied by higher-level permissions, or held through roles and groups, minus anything denied (sys.fn_my_permissions (Transact-SQL)[38]). Evaluation always happens in the caller's security context, so checking another principal means impersonating it, which requires IMPERSONATE permission on that principal:

Test a user's effective permissions on one table

EXECUTE AS USER = 'ReportUser';
SELECT * FROM fn_my_permissions('Reporting.MonthlyTotals', 'OBJECT');
REVERT;

EXECUTE AS USER stays inside the database. Azure SQL Database doesn't support EXECUTE AS LOGIN, so use EXECUTE AS USER there (Features comparison[19]). HAS_PERMS_BY_NAME tests one named permission, for example SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE'); (Azure SQL Database server roles[5]). A test run as a server administrator proves nothing about an application, because sysadmin members and the dbo user bypass the permission check (How GRANT, DENY and REVOKE combine above).

sys.database_permissions on its own doesn't summarize the rights a user inherits through fixed roles and other memberships, which is why the effective-permission functions above exist (sys.fn_my_permissions (Transact-SQL)[38]). To trace role-based rights, join sys.database_role_members to sys.database_principals on role_principal_id and member_principal_id, or use sys.server_role_members for server roles; in Azure SQL Database's virtual master, Microsoft's examples resolve SQL login members of server roles through sys.sql_logins (Azure SQL Database server roles[5]), while Microsoft Entra logins appear in sys.server_principals (Microsoft Entra authentication for Azure SQL[6]).

Find the failing check and scope first, repair mappings by SID rather than by name, and test permissions as the affected principal, never as an administrator.

Recognizing the exam scenarios

The scenarios in this section describe a requirement and contrast an option that satisfies it at the right scope with options that are broader, act on the wrong plane, or change the wrong principal. The table below pairs illustrative scenarios with an approach and the mechanism that rules out the tempting wrong option.

Scenario Approach Why the tempting option fails
Microsoft Entra users must connect to a new logical server Set the server's Microsoft Entra administrator SQL Server Contributor manages the Azure resource and doesn't set the administrator
An administrator subject to MFA connects from a desktop tool An interactive Microsoft Entra method Microsoft Entra Password authentication can't complete the MFA challenge
An Azure-hosted app needs database access without stored secrets Its managed identity, with a contained user and a narrow role A service principal's client secret must be stored, protected and rotated
Applications fail after Microsoft Entra-only authentication is enabled Move them to a Microsoft Entra method Rotating SQL passwords leaves the refused method unchanged
A legacy app using Windows authentication moves to Managed Instance unchanged Windows Authentication for Microsoft Entra principals A SQL login doesn't satisfy the client's Windows authentication flow
A legacy client sends a SQL password to a Windows-only SQL Server Mixed mode, an engine restart and a SQL login A same-named Windows login doesn't change the client's protocol
Microsoft Entra sign-in is required for a clustered SQL Server An availability group Failover cluster instances don't support Microsoft Entra authentication
Graph lookups fail when creating Microsoft Entra logins on a SQL Server VM Grant the VM's managed identity directory read permissions db_owner for the application changes database authorization only
A pipeline's service principal can't create Microsoft Entra users Give the server identity Graph read permissions Graph permissions on the pipeline's own identity aren't used for the lookup
A team needs identical read access A contained user for its Microsoft Entra group plus a role A group user only in master creates no principal in the application database
A reporting role needs future tables in one schema GRANT SELECT ON SCHEMA::Reporting SELECT at database scope also opens unrelated schemas
A deployment may create tables in one schema only CREATE TABLE plus ALTER on that schema db_ddladmin allows DDL throughout the database
An app may change data only through procedures EXECUTE on the procedures db_datawriter also allows direct table changes
An operator needs object definitions, not data VIEW DEFINITION on the objects db_datareader reveals business data
A DMV collector needs read-only state on Azure SQL Database ##MS_ServerStateReader## ##MS_ServerStateManager## also allows clearing caches
A user still has access after its direct grant is revoked Review role memberships, and DENY if access must be blocked REVOKE removes only the named grant; a new direct GRANT can't beat an inherited DENY
Restored database users can't sign in ALTER USER ... WITH LOGIN Dropping and recreating the user discards its grants
A contained user's access must end, but ALTER LOGIN ... DISABLE didn't stop it Drop the contained user in its own database Disabling a login doesn't affect contained users

When two options would both work, choose the one that meets the stated requirement with least privilege, on the correct plane (database permissions versus Azure roles), and acting on the principal the mechanism actually uses (Authorize database access[2]).

Authentication methods by platform: SQL vs Windows vs Microsoft Entra authentication

Platform or propertySQL authenticationWindows authenticationMicrosoft Entra authentication
What the engine verifiesA user name and password stored in master for a login, or in the database for a contained userAn existing Windows account mapped with CREATE LOGIN ... FROM WINDOWS; SQL stores no password for itA token from Microsoft Entra ID for a user, service principal or managed identity
Azure SQL DatabaseSupported until Microsoft Entra-only authentication is enabledNot supportedSupported after a Microsoft Entra administrator is set
Azure SQL Managed InstanceSupported until Microsoft Entra-only authentication is enabledWindows Authentication for Microsoft Entra principals, through Microsoft Entra Kerberos; disabled when Microsoft Entra-only authentication is enabledSupported after a Microsoft Entra administrator is set
SQL Server on Azure VMsRequires mixed mode, which takes effect after an engine restartSupported in either authentication modeSQL Server 2022 and later, through the SQL IaaS Agent extension and a managed identity that can read the directory

Decision tree

App on Windowsauthentication thatcan't change, movingto Managed Instance?WindowsAuthentication forMicrosoft EntraprincipalsYesNoMicrosoft EntraauthenticationUnattended workloadrunning in Azure?Managed identityno stored passwordYesNoA person who cansign in withMicrosoft Entra?Interactivemethodsupports MFAYesNoAnother unattendedworkload?Service principalrotate its secretYesNoSQL authentication loginrefused once Microsoft Entra-onlyauthentication is enabledAll paths need a SQL principal and grants

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

A Microsoft Entra administrator establishes Entra authentication for Azure SQL

Azure SQL Database and Azure SQL Managed Instance require a configured Microsoft Entra administrator before Microsoft Entra identities can connect. Set the administrator on the logical server or managed instance, then use an authorized Entra connection to establish the required SQL principals. The administrator property accepts one identity; selecting a group lets multiple administrators operate through that identity. Removing the administrator disables Entra-authenticated connections even when database users and their permissions still exist. Identities managed in another tenant must first be imported as external users into the SQL resource's associated tenant before contained-user provisioning, or gain access through an appropriately provisioned Entra group; configuring the administrator does not permit direct provisioning of an unrelated tenant's identity.

Trap Assigning the Azure SQL Server Contributor role to the intended administrator, which delegates resource management rather than configuring the server's Entra administrator property.

4 questions test this
Interactive administrators and unattended workloads need different Entra authentication methods

When a human administrator must satisfy multifactor authentication, use an interactive Microsoft Entra authentication method in a supporting client and enforce MFA through Conditional Access. For an unattended Azure workload, prefer its managed identity so Azure supplies tokens without an application-managed password. A service principal is another workload identity, but using a client secret introduces a credential that must be protected and rotated.

Trap Using Microsoft Entra Password authentication for a person subject to an MFA challenge, which cannot complete the required interactive verification.

5 questions test this
Microsoft Entra-only authentication changes which existing accounts can connect

Microsoft Entra-only authentication restricts Azure SQL connections to Entra identities rather than merely adding another sign-in option. Enabling it disables SQL-authenticated connections, including the SQL administrator, without requiring that their accounts be deleted. Validate application authentication methods before enabling it, and disable the setting when an authorized requirement genuinely needs another supported method.

Trap Rotating SQL login passwords as the remedy for failures after an Entra-only rollout, which leaves the rejected authentication method unchanged.

3 questions test this
SQL Server on Azure VMs uses a managed identity to resolve Entra principals

SQL Server 2022 and later on Azure Windows VMs can use Microsoft Entra authentication through the SQL IaaS Agent integration. Configure a system-assigned or user-assigned managed identity in the VM's tenant and grant that identity User.Read.All, GroupMember.Read.All, and Application.Read.All Microsoft Graph application permissions, or the broader Directory Readers role. SQL Server uses this identity for directory lookups when creating Entra logins and authenticating them, so a directory-permission failure belongs to that identity rather than the connecting application's database role.

Trap Granting db_owner to the application user to repair Microsoft Graph lookup failures, which changes database authorization rather than the VM identity's directory permissions.

3 questions test this
Azure Arc and SQL IaaS Agent provide different host integration paths for Entra authentication

For SQL Server 2022 and later on supported on-premises Windows or Linux hosts, Azure Arc registration with the Azure Connected Machine agent and Azure extension for SQL Server provides an Entra authentication configuration path. Azure-hosted SQL Server VMs use SQL IaaS Agent integration instead of requiring Arc registration for this purpose. Supported Windows installations also have a documented manual configuration path without Arc. Standalone instances and availability groups support Entra authentication, whereas SQL Server failover cluster instances do not.

Trap Selecting a SQL Server failover cluster instance for a deployment that requires Entra database sign-in, which uses an unsupported instance topology.

3 questions test this
SQL-authenticated logins require mixed mode on SQL Server

SQL Server Windows Authentication mode rejects SQL-authenticated logins; mixed mode permits both Windows and SQL authentication. In SSMS, change the instance's Properties, Security, Server authentication setting and restart the Database Engine for the new mode to take effect; restart SQL Server Agent as well if it is running. Changing from Windows-only to mixed mode does not automatically enable sa, and applications should use specifically provisioned logins rather than enabling sa merely to make SQL authentication work.

Trap Creating an identically named Windows login for a legacy SQL-password connection, which does not change the authentication protocol used by that client.

3 questions test this
Managed Instance can preserve Windows authentication through Microsoft Entra Kerberos

When a legacy application must retain Windows authentication while its database moves to Azure SQL Managed Instance, use Windows Authentication for Microsoft Entra principals. Microsoft Entra Kerberos supports this experience from appropriately configured Active Directory-joined, Entra-joined, or hybrid-joined devices without deploying Microsoft Entra Domain Services solely for the feature. This differs from changing the application to an Entra-aware driver's token authentication method and can preserve an otherwise unmodifiable authentication stack.

Trap Replacing an unchanged Integrated Security connection with a SQL login on the instance, which provisions a different credential type without satisfying the client's Windows authentication flow.

3 questions test this
Login provisioning must match the credential type and the platform's password controls

On SQL Server, CREATE LOGIN [AppLogin] WITH PASSWORD = '<strong-password>' provisions a SQL-password login, while CREATE LOGIN [CONTOSO\AppUser] FROM WINDOWS maps an existing Windows principal rather than storing its domain password in SQL. DEFAULT_DATABASE = [AppDb] selects the login's initial database when the client does not specify one; omitting it defaults the login to master. For SQL Server SQL logins, CHECK_POLICY defaults to ON and CHECK_EXPIRATION defaults to OFF; MUST_CHANGE requires both ON, and CHECK_EXPIRATION = ON cannot be combined with CHECK_POLICY = OFF. Azure SQL Database enforces password complexity rather than SQL Server's full Windows password-expiration and lockout-policy model, so those policy controls must not be assumed to operate identically on the two platforms.

Trap Combining MUST_CHANGE with CHECK_EXPIRATION = OFF for a SQL Server login, which makes login creation fail because mandatory first-use password change requires expiration enforcement.

3 questions test this
Creation-only principal delegation uses version-appropriate permissions at separate security scopes

For creation-only delegation on SQL Server 2022 and later, grant CREATE LOGIN at server scope and CREATE USER in the target database instead of broader authority to alter existing principals. Earlier SQL Server versions require ALTER ANY LOGIN or securityadmin membership for login creation, and ALTER ANY USER in the database for user creation. On SQL Server 2022 and later, GRANT CREATE LOGIN TO [ProvisionLogin] delegates login creation, while GRANT CREATE USER TO [ProvisionUser] must be issued in each intended database to delegate user creation there. Diagnose a provisioning permission failure at the scope of the failing statement rather than assuming that database user-creation authority also permits server login creation.

Trap Using securityadmin for a SQL Server 2022 delegate restricted to creating logins, which also grants broader login-management authority than the creation-only requirement.

3 questions test this
A login and its mapped database user serve different security scopes

A login authenticates at server scope, while a mapped user supplies the database identity to which roles and object permissions are assigned. Create the login first, then run CREATE USER [AppUser] FOR LOGIN [AppLogin] in each required database; the names can differ because the mapping uses security identifiers. One login can map to users in multiple databases, but only one user per database. A database role groups database principals, not server logins. CREATE USER [AppUser] FOR LOGIN [AppLogin] WITH DEFAULT_SCHEMA = [Reporting] makes Reporting the first schema searched for unqualified object names without granting access to its objects; correct the schema setting or qualify the object name for a resolution problem, and grant the required permission only for an authorization problem.

Trap Adding AppLogin directly to a database role, which uses a server principal where the role expects a database principal.

4 questions test this
Contained users reduce server dependencies when databases move

When a tenant database must move independently of its hosting server, a contained database user keeps authentication at database scope rather than depending on a login in master. A SQL contained user is created with CREATE USER [TenantUser] WITH PASSWORD = '<strong-password>'; the connection must explicitly name that database. Azure SQL Database supports this pattern directly, while SQL Server additionally requires contained database authentication to be enabled and the database configured for containment. Separate contained SQL users in multiple databases have independently managed passwords.

Trap Creating the tenant account only in virtual master to simplify database moves, which leaves the tenant database dependent on a principal outside its containment boundary.

3 questions test this
public supplies shared database rights while guest supplies a fallback database identity

public supplies baseline rights to every database user, whereas enabled guest access in SQL Server lets authenticated logins without their own database user access the database with guest's permissions. Every database user belongs to public and cannot be removed from that role, so grant application-specific business access to a dedicated role instead of public and review public when removing unexpected baseline access. A denial through public can also affect users who receive grants through other roles because permission evaluation includes their public membership. The guest user already exists and is enabled with GRANT CONNECT TO guest, not CREATE USER, so review that fallback access path independently of public membership and explicitly mapped users.

Trap Using public as a convenient reporting role for one department, which gives the reporting entitlement to unrelated current and future database users.

4 questions test this
A database user for an Entra group separates SQL permissions from individual membership

When a team needs the same database access, connect with an authorized Entra identity and create the group user in the target database with CREATE USER [TeamReaders] FROM EXTERNAL PROVIDER. Assign that user to a suitable database role, for example ALTER ROLE db_datareader ADD MEMBER [TeamReaders] when all user tables and views must be readable. Individual onboarding and removal can then occur through Entra group membership rather than separate SQL grants for every person. Scope the role more narrowly when the group needs only selected objects. If database login fails despite correct principal provisioning and SQL grants, check the connecting identity's group memberships: Entra users and service principals belonging to more than 2048 Entra security groups are unsupported for database login to Azure SQL Database and Azure SQL Managed Instance.

Trap Provisioning the group only as a user in master, which does not establish its database principal and role membership in the application database.

4 questions test this

A contained user with the same name as a server login is an independent principal, so matching names do not establish a link to that login's privileges. CREATE USER [IdentityName] FROM EXTERNAL PROVIDER creates a contained Entra database user, whereas CREATE LOGIN [IdentityName] FROM EXTERNAL PROVIDER followed by CREATE USER [IdentityName] FROM LOGIN [IdentityName] establishes a login-based user. Use the login-based pattern when the database identity must be linked to the server principal's permissions. When diagnosing Azure SQL Entra mappings, inspect sys.database_principals: a contained user's SID is 16 bytes, whereas a login-based user's SID is 18 bytes with an AADE suffix; remove that suffix before correlating the user's SID with its login in sys.server_principals rather than treating the unequal raw SIDs as proof of an orphaned user.

Trap Changing the contained user's display name to match the privileged login, which leaves the underlying principal relationship unchanged.

4 questions test this
The SQL resource's identity supplies directory lookup authorization

For service-principal-driven CREATE USER ... FROM EXTERNAL PROVIDER in Azure SQL Database or Managed Instance, configure the SQL resource's managed identity with directory-read access so the engine can resolve the requested Entra principal. Azure SQL cannot impersonate the calling application for Microsoft Graph lookups, so successful provisioning by an interactive Entra user does not establish that the same statement will work under automation. Assigning Directory Readers to the server or instance identity is a supported way to supply directory-read access and requires a Privileged Role Administrator or higher tenant role; the provisioning caller separately needs SQL permission to create the principal. If SQL error 33134 contains an underlying Entra access-denied or MFA-enrollment message, investigate Conditional Access for Microsoft Graph application 00000003-0000-0000-c000-000000000000 rather than automatically attributing the failure to a missing directory grant or adding broader SQL roles.

Trap Granting Graph permissions only to the deployment application's identity, which leaves the SQL server identity unable to perform its own required directory lookup.

4 questions test this
Entra user creation must identify the intended directory object unambiguously

On Azure SQL Database or Managed Instance, use ordinary name-based provisioning for a unique name and WITH OBJECT_ID to disambiguate a nonunique group or application display name, even when the identity exists and the caller has provisioning permission. For Azure SQL contained-user provisioning, CREATE USER [adrian@contoso.com] FROM EXTERNAL PROVIDER identifies an individual by user principal name, whereas groups, service principals, and managed identities use their display names. For an application, CREATE USER [myapp_suffix] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '<service-principal-object-id>' uses the display name plus a distinguishing suffix as the SQL alias; take that object ID from Enterprise applications in the SQL resource's tenant, not the application/client ID or the different application-object ID under App registrations. As an alternative to validated lookup in Azure SQL Database, use CREATE USER [IdentityAlias] WITH SID = <binary-identity-id>, TYPE = E for an Entra user or service principal, including a managed identity, or TYPE = X for a group. For this SID-and-TYPE route, convert the user or group object ID, or the application or managed identity client ID, to binary(16) and independently verify the supplied identity because the Database Engine does not validate it in Entra.

Trap Using the App registrations object ID in WITH OBJECT_ID, which identifies the application object instead of the tenant's enterprise-application service principal.

4 questions test this
Azure SQL Database supports explicit Entra logins for additional administrative identities

In Azure SQL Database, the Microsoft Entra administrator must create the first Entra login in virtual master with CREATE LOGIN [IdentityName] FROM EXTERNAL PROVIDER; a SQL-authenticated administrator cannot perform this Entra provisioning. An Entra group login cannot be assigned Azure SQL Database fixed server roles or made a database owner, so use a supported individual or application principal when those administrative capabilities are required. Authenticating through a group login does not implicitly create each member's individual login or default schema. If a group member cannot perform CREATE SCHEMA because that individual provisioning is absent, create the individual's Entra login or assign a default schema to the group. An identity authenticating as the configured Entra administrator, individually or through its administrator group, retains administrator effective permissions even if a separate restricted Entra login is created for it, because the administrator identity takes precedence over that login.

Trap Using an Entra group login as the member of ##MS_LoginManager## for team-wide delegation, which targets a fixed server role that does not accept Entra group logins.

4 questions test this
Database-wide read and write roles grant independent capabilities

db_datareader permits reading all user tables and views, while db_datawriter permits inserting, updating, and deleting rows in user tables. Workloads that must both read and modify data commonly need both capabilities, whereas a reporting account needs only the read capability. These fixed roles are database-wide, so selected-table access calls for a custom role with explicit object permissions. Manage membership with ALTER ROLE [RoleName] ADD MEMBER or DROP MEMBER rather than recreating the user.

Trap Assigning db_ddladmin to a data-entry application, which delegates database-definition operations instead of the required row-level read and write permissions.

3 questions test this
Managing role membership is different from granting database access

db_securityadmin can manage permissions and membership of custom database roles, while db_accessadmin manages who can access the database. If an administrator must manage only one custom role, grant ALTER ON ROLE::[ApplicationRole] rather than a database-wide security role. db_securityadmin does not delegate membership management of fixed database roles, and its broad permission-management capability can enable privilege escalation. Choose the smallest administration boundary that meets the requirement.

Trap Assigning db_accessadmin to a team that must maintain custom role memberships, which provides user-access administration rather than the required role-management authority.

4 questions test this
Azure SQL Database distinguishes database provisioning from login management

In Azure SQL Database virtual master, ##MS_DatabaseManager## delegates database creation and deletion, while ##MS_LoginManager## delegates login creation and deletion. Prefer these fixed server roles over the dbmanager and loginmanager database roles: use ALTER SERVER ROLE [##MS_LoginManager##] ADD MEMBER [ProvisionLogin] for login membership, whereas the older roles accept database users in master. ##MS_DatabaseConnector## provides CONNECT to all databases without requiring a separately created database user, but does not by itself grant SELECT or other data permissions. A database-creation delegate becomes owner of databases it creates but does not automatically gain data access to every other database. For SQL-authenticated database creation through the older dbmanager route, use a login-mapped user rather than a password-contained user in master.

Trap Granting db_owner in one application database to a person who must create other databases, which grants authority at the wrong database scope.

4 questions test this
SSMS permission dialogs apply rights to the selected securable and principal

In SSMS, open a securable's Properties and Permissions page to select a database user or role and configure the operation needed on that securable. Choose the table for table-specific SELECT or UPDATE rights, or the database for database-scoped permissions, rather than changing server-role membership. The Grant, With Grant, and Deny choices represent permission use, delegated grant authority, and explicit rejection respectively. The grantor must hold the permission with GRANT OPTION or a higher permission that implies it before delegating that permission.

Trap Using the server login's role-membership page for a single stored procedure entitlement, which configures server roles rather than that object's database permission.

4 questions test this
Database-wide fixed roles are unsuitable for narrowly scoped administration or data exceptions

Use db_owner only when all database configuration and maintenance authority is required; db_ddladmin instead delegates database-wide DDL and can enable privilege escalation by altering code executed with higher privileges. The backup-specific db_backupoperator role is not a useful delegation option in Azure SQL Database because native BACKUP and RESTORE statements are unavailable there. db_denydatareader applies a database-wide SELECT denial to user tables and views, while db_denydatawriter applies INSERT, UPDATE, and DELETE denials to user tables. When a role must retain ordinary table access to selected objects, use narrowly scoped explicit permissions instead of adding it to a database-wide denial role.

Trap Assigning db_ddladmin for a deployment restricted to one schema, which permits definition changes throughout the database and can affect code run by more privileged principals.

4 questions test this
A custom role needs separately configured permissions and membership

CREATE ROLE [ReportRole] AUTHORIZATION [dbo] creates a database role but does not grant its application permissions or enroll its users. Run GRANT SELECT ON OBJECT::Reporting.MonthlyTotals TO [ReportRole] and ALTER ROLE [ReportRole] ADD MEMBER [ReportUser] as separate steps in the target database. On SQL Server or Managed Instance, server-wide delegation instead uses CREATE SERVER ROLE [DiagnosticRole], a server-scoped grant such as GRANT VIEW SERVER STATE TO [DiagnosticRole], and ALTER SERVER ROLE [DiagnosticRole] ADD MEMBER [OperatorLogin]. Azure SQL Database does not support user-defined server roles, so choose a custom database role for database permissions or an appropriate fixed server role for supported logical-server delegation.

4 questions test this
Delegated permissions must match both the required operation and securable scope

Match delegated permissions to both the required operation and securable scope: GRANT SELECT ON SCHEMA::Reporting TO [ReportRole] covers the schema's current and subsequently created objects, whereas a smaller entitlement requires grants on individual objects or selected columns. CONTROL confers ownership-like authority, and ALTER on a schema permits definition changes to contained objects, so neither is a substitute for a read-only requirement. Creating a schema-contained object requires both its database-level CREATE permission and ALTER on the intended schema, for example GRANT CREATE TABLE TO [DeploymentRole] together with GRANT ALTER ON SCHEMA::Reporting TO [DeploymentRole]. REFERENCES on a referenced table authorizes a foreign-key dependency, and REFERENCES on an object is needed for schema-bound views or functions referencing it; this permission does not grant SELECT access to its data.

Trap Granting SELECT at database scope to accommodate future reporting tables, which also includes unrelated schemas outside the required reporting boundary.

3 questions test this
Stored procedure execution can expose an approved operation without exposing unrestricted table access

When an application should perform only a defined database operation, grant EXECUTE on the stored procedure rather than broad direct permissions on the underlying tables. An unbroken ownership chain between a procedure and its referenced objects can avoid separate checks on those objects, while a changed ownership boundary can require additional authorization. Deliberate module execution context can provide controlled access, but it should be scoped to the operation rather than granting the caller permanent administrative membership. CREATE USER [ModuleContext] WITHOUT LOGIN and users mapped with FROM CERTIFICATE or FROM ASYMMETRIC KEY can hold permissions but cannot authenticate; certificate- or asymmetric-key-mapped logins likewise serve code signing rather than client connections. As an alternative to changing execution context, create a certificate-mapped user, grant it the required rights, and sign the procedure with ADD SIGNATURE TO [ProcedureName] BY CERTIFICATE [SigningCertificate] so those rights supplement the caller's permissions during signed execution without permanently elevating the caller.

Trap Assigning db_datawriter to an application that may modify data only through validation procedures, which also permits direct changes outside those procedures.

3 questions test this
Azure resource management and SQL data access have separate authorization systems

Azure RBAC roles such as SQL DB Contributor and SQL Server Contributor authorize management operations on Azure resources, not SQL query access to database data. A managed identity or human Entra identity still needs a SQL principal and appropriate database permissions to execute queries. Conversely, granting a database user SELECT does not permit that identity to resize the Azure resource. Delegate each plane independently when an operational role needs both kinds of access.

Trap Assigning subscription Contributor to a reporting identity that receives SELECT permission errors, which expands resource-management privileges without supplying SQL data-plane authorization.

3 questions test this
Missing catalog rows can reflect metadata visibility rather than missing objects

SQL metadata visibility normally exposes securables a user owns or has some permission on, so a catalog query can return no row and a metadata function can return NULL for an existing object. When an operator must inspect an object's definition without reading its data, grant VIEW DEFINITION at the object scope instead of a data-access role. Broaden that permission to database scope only when inspection of all definitions is required. Test metadata queries under the intended user's context before concluding that a deployment omitted an object.

Trap Assigning db_datareader to an operator who needs definitions only, which reveals business data beyond the metadata inspection requirement.

3 questions test this
Diagnostic delegation should distinguish state reading from state changes and sensitive definition access

For Azure SQL Database diagnostics, choose ##MS_ServerStateReader## for VIEW SERVER STATE and related database-state rights; ##MS_ServerStateManager## adds ALTER SERVER STATE for management operations such as cache clearing. ##MS_DefinitionReader## exposes general catalog definitions through VIEW ANY DEFINITION, whereas ##MS_SecurityDefinitionReader## targets security definitions and additionally grants VIEW ANY CRYPTOGRAPHICALLY SECURED DEFINITION, including access to SQL-login password hashes. Assign the security-definition role only to trusted personnel who require that sensitive access, rather than treating it as a harmless subset of definition reading. For propagated database permissions to be usable, the login needs a matching database user or connectivity through ##MS_DatabaseConnector##, including for virtual master.

Trap Using ##MS_ServerStateManager## for a read-only DMV collector, which also permits state-changing management operations such as clearing caches.

4 questions test this
REVOKE removes an explicit permission decision while DENY blocks an otherwise available grant

GRANT allows an operation, DENY generally overrides grants received through other roles or groups, and REVOKE removes the explicit grant or denial being targeted. A revoked direct grant can leave access intact through role membership, so review all relevant principals before deciding that access was removed. Permission checks do not constrain sysadmin members or object owners in the same way, and a column-level GRANT is a documented exception to a table-level DENY. Avoid assuming that a direct GRANT overrides an inherited DENY.

Trap Issuing another direct GRANT after a denial inherited from a role, which does not remove the effective denial.

3 questions test this
A restored user's login mapping must match the destination login's SID

A login-mapped user can become orphaned after a restore or after its login is dropped and recreated, even when a destination login has the same name. Compare sys.database_principals with the corresponding login security identifiers rather than relying on names. Use ALTER USER [DatabaseUser] WITH LOGIN = [ExistingLogin] to remap the user, or recreate the intended SQL login with the original SID when preserving that identity is appropriate. Keeping the existing database user preserves its role memberships and permissions.

Trap Dropping and recreating the database user to match a login name, which discards the existing user identity and its grants instead of repairing the mapping.

3 questions test this
Effective-permission tests must run in the security context being diagnosed

sys.fn_my_permissions(NULL, 'DATABASE') reports the current caller's effective database permissions, while HAS_PERMS_BY_NAME can test a particular permission on an object. Use authorized EXECUTE AS USER = 'UserName' and REVERT to examine a database user's actual context; sys.database_permissions alone does not summarize all rights inherited through fixed roles and other memberships. EXECUTE AS USER stays within the database, whereas EXECUTE AS LOGIN is a server-scoped option on supporting instance platforms and is unavailable in Azure SQL Database. Impersonation requires the relevant authority on the principal. To trace inherited role rights, join sys.database_role_members to sys.database_principals on role_principal_id and member_principal_id, or inspect sys.server_role_members for server roles; in Azure SQL Database virtual master, resolve SQL-login members through sys.sql_logins and Entra-login members through sys.server_principals.

Trap Running a successful permission test as the server administrator and treating it as proof of application access, which evaluates a different security token.

3 questions test this
Disabling an Entra login revokes its login-based connection path rather than every path for that identity

In Azure SQL Database, ALTER LOGIN [IdentityName] DISABLE blocks connections through that Entra login and its login-based users, but leaves independent contained users unaffected. Disablement is unsupported for Entra group logins and cannot be used to disable an individual who belongs to an Entra group login in master, including an administrator group. Entra login permission and server-role changes normally take effect when the login reconnects rather than changing existing sessions' rights. When cached authorization delays the change, an authorized administrator can clear the affected database's authentication and token-permission caches with DBCC FLUSHAUTHCACHE and DBCC FREESYSTEMCACHE('TokenAndPermUserStore') WITH NO_INFOMSGS, then require reconnection before retesting.

Trap Using ALTER LOGIN DISABLE to remove access supplied by an independent contained user, which changes a server principal outside that user's authentication path.

3 questions test this

Also tested in

References

  1. https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication
  2. https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage
  3. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine
  4. https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine
  5. https://learn.microsoft.com/en-us/azure/azure-sql/database/security-server-roles
  6. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview
  7. https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles/databases
  8. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  9. https://learn.microsoft.com/en-us/azure/azure-sql/database/firewall-configure
  10. https://learn.microsoft.com/en-us/sql/relational-databases/security/row-level-security
  11. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
  12. https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server
  13. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
  14. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure
  15. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/configure-azure-ad-authentication-for-sql-vm
  16. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication
  17. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/azure-ad-authentication-sql-server-overview
  18. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode
  19. https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
  20. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/winauth-azuread-overview
  21. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql
  22. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles
  23. https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable
  24. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-logins
  25. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-microsoft-entra-create-users-with-nonunique-names
  26. https://learn.microsoft.com/en-us/entra/identity/enterprise-apps/what-is-application-management
  27. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal
  28. https://learn.microsoft.com/en-us/sql/t-sql/statements/deny-transact-sql
  29. https://learn.microsoft.com/en-us/sql/t-sql/statements/revoke-transact-sql
  30. https://learn.microsoft.com/en-us/sql/relational-databases/security/metadata-visibility-configuration
  31. https://learn.microsoft.com/en-us/azure/role-based-access-control/deny-assignments
  32. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql
  33. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-role-transact-sql
  34. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-server-role-transact-sql
  35. https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/grant-a-permission-to-a-principal
  36. https://learn.microsoft.com/en-us/sql/relational-databases/security/permissions-or-securables-page
  37. https://learn.microsoft.com/en-us/sql/t-sql/statements/add-signature-transact-sql
  38. https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-my-permissions-transact-sql