5.3 Security Principals and Entra-Backed Users

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.

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:

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.