Study guide

5.4 Permissions, Least Privilege, and T-SQL Management

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.4 Permissions, Least Privilege, and T-SQL Management

Key Takeaways Explain This Topic AI

GRANT assigns a permission, REVOKE removes a GRANT or DENY, and DENY explicitly blocks a permission - DENY always wins and overrides any GRANT the principal receives elsewhere

The securable hierarchy (server -> database -> schema -> object) lets you grant at higher scopes and inherit downward; granting SELECT on a schema is preferred over granting SELECT on every table

Least privilege means granting the narrowest securable scope and minimum role membership needed - prefer user-defined roles over db_owner, and prefer db_datareader over db_owner for read-only reporting

Ownership chaining skips permission checks on downstream objects when the caller and callee share an owner; understanding this is required to avoid unintended access grants and to design secure view/procedure layers

Dynamic data masking and row-level security (RLS) are separate from object permissions: masking is applied at the column level on top of any GRANT, RLS filters rows based on a predicate function, and neither substitutes for permission grants

Last updated: August 2026

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.