Study guide
5.3 Security Principals and Entra-Backed Users
DP-300: Administering Microsoft Azure SQL Solutions Exam
Track your progress as you read
Finished sections get a check mark.
1 Introduction & Exam Overview
1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources
2 Planning and Deploying Azure SQL Solutions
2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching
3 Configuring Resources for Scale and Performance
3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression
4 Planning and Implementing a Migration Strategy
4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting
5 Configuring Database Authentication and Authorization
5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management
6 Implementing Security for Data at Rest and in Transit
6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints
7 Implementing Compliance Controls for Sensitive Data
7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL
8 Monitoring Resource Activity and Performance
8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events
9 Monitoring and Optimizing Query Performance
9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights
10 Configuring Database Solutions for Optimal Performance
10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing
11 Creating and Managing SQL Server Agent Jobs
11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates
12 Automating Deployments and Database Tasks in Azure
12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks
13 Planning an HA/DR Strategy and Backup and Restore
13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations
14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups
14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups
15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting
15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions
5.3 Security Principals and Entra-Backed Users
Key Takeaways Explain This Topic AI
A login is a server-scoped principal authenticated at the server; a user is a database-scoped principal mapped to a login (or contained, with no login) - the login/user distinction is the foundation of SQL Server security
Contained database users authenticate at the database boundary and travel with the database, eliminating the master-database dependency that server-login users impose
Fixed server roles (sysadmin, securityadmin, dbcreator, etc.) grant server-scoped permissions; fixed database roles (db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin) grant database-scoped permissions and cannot be dropped
Entra accounts, Entra groups, and Entra applications can all be created as users or logins with FROM EXTERNAL PROVIDER, and mapping an Entra group as a principal lets membership changes in Entra flow without touching SQL
Schema ownership controls the default schema for objects created without qualification and affects ownership chaining; assigning a schema to a role instead of an individual user simplifies ownership over time
Last updated: August 2026
Logins vs Users: The Foundational Distinction
Every security principal in SQL Server lives in one of two scopes. A login is a server-scoped principal: it lives in the master database (for SQL Database, on the logical server), is authenticated when a session is established, and is identified by a SID at the server level. A user is a database-scoped principal: it lives inside one user database, is mapped (usually) to a login via a matching SID, and is the entity to which database permissions are granted. The classic formulation: "logins get you in the front door; users let you sit in a room." A login without a user mapping in a database can connect to the server but cannot use that database (except through the guest schema, if enabled). A user without a login is a contained user that authenticates only in that one database.
This distinction drives almost every security-related exam question. When a scenario describes a user who can connect to the server but receives "cannot access database X," the missing piece is a user mapping in database X - not a permission, not a role, a user. When a scenario describes a database that is moved to a new server and all its users become orphaned, the cause is that the logins (with their SIDs) lived on the old server and did not travel with the database; contained users avoid this entirely.
Principal Scope Lives in Created with
SQL login Server master CREATE LOGIN ... WITH PASSWORD = ...
Entra login Server master (MI) CREATE LOGIN ... FROM EXTERNAL PROVIDER
SQL user (mapped) Database user DB CREATE USER ... FOR LOGIN ...
- Entra user (contained) Database user DB CREATE USER ... FROM EXTERNAL PROVIDER
- Entra user (mapped) Database user DB CREATE USER ... FOR LOGIN ... (MI only)
- Contained Database Users
A contained database user is created directly in the user database with no corresponding login in master. SQL Server supports two kinds: SQL-authenticated contained users ( CREATE USER AppUser WITH PASSWORD = '...' ) and Entra-backed contained users ( CREATE USER [alice@contoso.com] FROM EXTERNAL PROVIDER ). Both authenticate at the database boundary, which means:
The database is portable - move it to another server or instance and all contained users travel with it; no sp_change_users_login to fix orphaned SIDs.
The master database is no longer a single point of identity - you can grant access in each database independently.
The contained database feature must be enabled ( CONTAINMENT = PARTIAL ); SQL Database enables this by default, on-prem SQL Server and SQL MI require explicit configuration.
The exam presents contained users as the recommended pattern for application access in Azure SQL Database, where the database is the unit of deployment. Server logins remain necessary when a principal needs server-level roles or cross-database administration on MI.
Fixed Server and Database Roles
SQL Server ships with a fixed set of roles that cannot be dropped or modified in membership semantics. The fixed server roles grant server-scoped privileges:
sysadmin - full server control; the DBA equivalent of root.
serveradmin - server configuration and shutdown.
securityadmin - manage logins and permissions.
dbcreator - create, alter, and drop databases.
diskadmin (legacy) - manage disk files.
processadmin - kill processes.
Azure SQL adds its own special fixed server-level roles , which use the ##MS_ prefix and ## suffix to distinguish them from user-created principals. Azure SQL Database provides seven: ##MS_DatabaseConnector## (CONNECT to any database), ##MS_DatabaseManager## (create and drop databases), ##MS_LoginManager## (create and drop logins), ##MS_DefinitionReader## (VIEW ANY DEFINITION), ##MS_SecurityDefinitionReader## (VIEW ANY SECURITY DEFINITION), ##MS_ServerStateReader## (VIEW SERVER STATE), and ##MS_ServerStateManager## (adds ALTER SERVER STATE). The same roles were introduced in SQL Server 2022, and ##MS_ServerPerformanceStateReader## is the narrower performance-state variant used, for example, to grant database watcher its collection permissions. Use these instead of sysadmin when a login needs one specific server-scoped capability.
The fixed database roles grant database-scoped privileges:
- Role Granted permission
- db_owner All permissions in the database (effectively the database's sysadmin)
- db_securityadmin Manage roles and permissions within the database
- db_accessadmin Add or remove database users
- db_datareader SELECT all tables/views in the database
- db_datawriter INSERT, UPDATE, DELETE on all tables
- db_ddladmin CREATE/ALTER/DROP any object
- db_backupoperator Back up the database
- db_denydatareader / db_denydatawriter Explicit DENY of read/write (overrides GRANT)
The db_denydatareader and db_denydatawriter roles are special: they apply a DENY that overrides any GRANT the user receives elsewhere, which is how you implement a "read-only" enforcement for a principal that might otherwise inherit write access through group membership.
User-Defined Roles and Entra Groups as Principals
Fixed roles are coarse. The best-practice pattern for least privilege is to create user-defined database roles , grant the role a narrow set of permissions, and add users (or Entra groups) to the role. Example: CREATE ROLE SalesReader;
- GRANT SELECT ON SCHEMA::Sales TO SalesReader;
- ALTER ROLE SalesReader ADD MEMBER [SalesReadersGroup@contoso.com];
Creating the role once and adding members (or Entra groups) as they join keeps the permission set stable and auditable. A frequent exam trap is to grant permissions directly to a user instead of a role; while technically valid, it is a maintenance and audit liability and the wrong answer when a scenario asks for a maintainable least-privilege design.
Entra groups as principals is the recommended model for team-based access: create one Entra group per role (e.g., "Sales Readers"), map it as a database user or MI login with FROM EXTERNAL PROVIDER , and manage membership entirely in Entra. The database engine expands group membership at login time using the server identity's directory read, so adding a user to the Entra group grants database access with no SQL-side change . This is the pattern the exam rewards for "centralize access management" scenarios.
Creating Users from Entra Identities
The CREATE USER ... FROM EXTERNAL PROVIDER syntax accepts three kinds of Entra principal:
Entra user - CREATE USER [alice@contoso.com] FROM EXTERNAL PROVIDER .
Entra group - CREATE USER [SalesReaders@contoso.com] FROM EXTERNAL PROVIDER ; every member of the group inherits the user's database permissions when they connect.
Entra application (service principal) - the application can be named by its display name or, more reliably, by its application client ID; CREATE USER [my-api-app] FROM EXTERNAL PROVIDER creates a user the service principal can map to when it connects with Authentication=Active Directory Service Principal .
On SQL Managed Instance, the parallel syntax CREATE LOGIN ... FROM EXTERNAL PROVIDER creates server-level logins for the same three principal kinds, after which you CREATE USER ... FOR LOGIN ... in each database. On SQL Database, contained users are the norm; server logins are reserved for server-role scenarios.
Schema Ownership
A schema is a namespace for database objects and a securable in its own right. Every schema has an owner (a database principal). When a user creates an object without qualifying the schema, the object is placed in the user's default schema . The default schema for a new user is dbo unless you specify otherwise ( ALTER USER [alice] WITH DEFAULT_SCHEMA = Sales ).
Schema ownership matters for two reasons on the exam. First, ownership chaining : when objects in the same schema share an owner, SQL Server skips permission checks on downstream objects - the chain is unbroken if the owner is the same. This is why you grant SELECT on a schema rather than per-object, and why changing schema ownership can break or grant unintended access. Second, assigning a schema's ownership to a role rather than an individual user keeps ownership stable as team members join and leave - the role is the owner, and the objects created in that schema inherit the role's ownership.