5.4 Permissions, Least Privilege, and T-SQL Management

GRANT, DENY, REVOKE: The Three Permission Verbs

Three T-SQL statements control permissions, and their semantics are heavily tested:

• GRANT assigns a permission to a principal. GRANT SELECT ON SCHEMA::Sales TO SalesReader;
• DENY explicitly blocks a permission. DENY always wins: if a principal is granted a permission through one path and denied through another, the DENY takes precedence, regardless of the order in which they were applied.
• REVOKE removes a previously granted or denied permission, returning the principal to the default state (no explicit grant or deny). REVOKE SELECT ON SCHEMA::Sales FROM SalesReader; removes the grant; add CASCADE to revoke the permission from any principals to whom SalesReader re-granted it.

The order of precedence is: DENY > GRANT > no permission. The db_denydatareader and db_denydatawriter fixed roles exist precisely to apply a DENY across the whole database, useful for ensuring an account used for monitoring or auditing can never write to any table even if it inherits write access through some group membership.

A subtle trap: REVOKE without CASCADE leaves downstream grants in place, which can leave access in place through a re-grant path. When cleaning up permissions, REVOKE ... CASCADE is safer for revoking access that may have propagated.

The Securables Hierarchy

SQL Server organizes securables in a four-tier hierarchy. Permissions granted at a higher scope inherit downward to children of the same securable class:

Server
  └─ Database
       └─ Schema
            └─ Object (table, view, procedure, function)

Granting SELECT on a schema grants SELECT on every existing and future table in that schema - which is the recommended granularity for most application roles. Granting at the database level (GRANT SELECT ON DATABASE::Sales TO ...) grants SELECT on every schema in the database, which is usually too broad. Object-level grants (GRANT SELECT ON dbo.Customers TO ...) are the narrowest and most precise but do not scale to new tables.

Permissions are scoped to the class: server-level permissions (CONTROL SERVER, VIEW ANY DATABASE, ALTER ANY LOGIN) live at the server; database permissions (SELECT, INSERT, EXECUTE, ALTER, CONTROL) live at the database or below. The CONTROL permission grants all permissions on a securable and its children - effectively ownership without taking ownership. Granting CONTROL on a database is close to db_owner; grant it sparingly.

Object-Level Permissions and Graphical Tools

While T-SQL is the canonical way to manage permissions, both SQL Server Management Studio (SSMS) and the Azure portal provide graphical editors. In SSMS, the Securables page of a principal's Properties dialog lets you add objects, select the grantor, and tick Grant/With Grant/Deny per permission. In the Azure portal, the SQL Database's Access control (IAM) blade manages Azure role-based access control (Azure RBAC, the management-plane), while database-plane permissions must be set through T-SQL or SSMS - a common confusion point. The exam distinguishes the two planes: Azure RBAC controls who can manage the Azure resource (scale, set admin, view metrics); SQL permissions control who can read or write data. Granting someone "Contributor" on the Azure resource does not grant them data access.

Object-level permission auditing is essential for least-privilege verification. The query below lists every explicit permission granted in the current database:

SELECT
    USER_NAME(grantee_principal_id) AS Grantee,
    permission_name,
    state_desc,
    OBJECT_SCHEMA_NAME(major_id) AS SchemaName,
    OBJECT_NAME(major_id) AS ObjectName
FROM sys.database_permissions
WHERE class_desc = 'OBJECT_OR_COLUMN'
ORDER BY Grantee, SchemaName, ObjectName;

For server-level permissions, query sys.server_permissions joined to sys.server_principals. Run these audits periodically to detect permission drift, especially after personnel changes.

Least Privilege vs db_owner Over-Granting

Least privilege is the design principle that every principal receives the minimum permissions and role memberships required to do its job - and no more. The exam tests this primarily by contrasting it with the common anti-pattern of granting db_owner to applications and users because it is easy.

db_owner grants all permissions in the database; a member can drop tables, change schemas, grant permissions to others, and disable security features. For a reporting account, the correct role is db_datareader (read across all tables) or, better, a user-defined role with SELECT on specific schemas. For an application service account, the correct design is a user-defined role with the specific INSERT/UPDATE/SELECT/EXECUTE permissions the application needs - not db_owner. The trap pattern on the exam: a scenario describes an account that only needs to read three tables but has been granted db_owner "for simplicity," and the question asks for the least-privilege remediation - the answer is to drop the db_owner membership and grant SELECT on the three tables (or the schema containing them) to a user-defined role, then add the account to that role.

A practical distinction: db_datareader + db_datawriter covers most application needs and is safer than db_owner, but still over-grants to schemas the application should not touch. A user-defined role scoped to the specific schemas used by the application is the least-privilege ideal.

Ownership Chaining

Ownership chaining is the mechanism by which SQL Server skips permission checks on downstream objects when the caller and the callee share the same owner. When a user with SELECT on a view executes the view, and the view and the underlying tables share the same owner, SQL Server does not check the user's permissions on the underlying tables - the chain is unbroken.

This has two consequences the exam tests. First, it is a useful pattern for exposing data through views without granting direct table access: grant SELECT on the view, and the user sees the view's result without needing SELECT on the underlying tables. Second, it can produce unintended access if ownership changes - if the view's owner differs from the underlying tables' owner (a broken chain), SQL Server falls back to checking the caller's permissions on the underlying tables, which can either block access (caller has no permission) or surface an unintended permission (caller happens to have direct permission).

To check ownership, query sys.objects:

SELECT name, schema_id, SCHEMA_NAME(schema_id) AS SchemaName,
       USER_NAME(SCHEMA_ID(schema_id)) AS SchemaOwner
FROM sys.objects
WHERE type IN ('U', 'V');

The CROSS APPLY pattern of EXECUTE AS OWNER on a procedure is the complementary mechanism: the procedure runs as its owner, and permission checks on downstream objects use the owner's permissions. This is the basis of secure stored-procedure access patterns - grant EXECUTE on the procedure, and the caller can use the procedure's logic without direct table permissions.

Permissions vs Dynamic Data Masking vs Row-Level Security

The exam separates three security layers that operate independently:

• Permissions (GRANT/DENY/REVOKE) control whether a principal can access an object at all. Without SELECT on a table, the principal sees nothing.
• Dynamic data masking (DDM) is applied at the column level on top of any GRANT. A user with SELECT sees the rows but the masked columns show a masked value (e.g., XXXX-XX-1234 for a credit card) unless the user has the UNMASK permission. DDM does not replace permissions - it limits what a SELECT returns.
• Row-level security (RLS) filters rows based on a predicate function. A user with SELECT sees only the rows the predicate permits. RLS is implemented with a security predicate on a table and a security function that evaluates the caller; it does not grant SELECT, it restricts the rows returned to principals who already have SELECT.

The separation matters for least-privilege design: a common exam trap is to assume that adding a user to a role with SELECT on a masked column is sufficient to expose the masked data - it is, but the user still sees the masked value unless granted UNMASK. Conversely, RLS does not substitute for SELECT - you grant SELECT on the table, then apply the RLS predicate to filter which rows the SELECT returns. The layers compose: a principal needs the permission to access the object, the predicate to determine which rows, and the absence of a column mask (or UNMASK permission) to see the unmasked value.

T-SQL Management Patterns

Putting it together, a least-privilege T-SQL recipe for a reporting application:

-- 1. Create a contained Entra user for the reporting service principal
CREATE USER [reporting-api] FROM EXTERNAL PROVIDER;

-- 2. Create a user-defined role scoped narrowly
CREATE ROLE SalesReporter;
GRANT SELECT ON SCHEMA::Sales TO SalesReporter;
DENY SELECT ON SCHEMA::Sales.Sensitive TO SalesReporter; -- block a sub-schema
ALTER ROLE SalesReporter ADD MEMBER [reporting-api];

-- 3. Apply row-level security to restrict rows by region
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE dbo.fn_RegionFilter() ON dbo.Orders;

This recipe illustrates the layered model: an Entra principal (authentication), a contained user (database principal), a user-defined role (least privilege), a DENY to carve out an exception, and an RLS predicate to filter rows. The exam rewards answers that build security from this layered model rather than collapsing everything into db_owner.