Chapter 14 · Security: Logins, Users, Roles, Permissions, Encryption, and Auditing
SQL Server Audit, Extended Events for Security, Row-Level Security, Dynamic Data Masking, and Hardening
Combine SQL Server Audit, Extended Events, Row-Level Security and Dynamic Data Masking with protected evidence, policy tests and practical hardening.
Learning outcomes
Encryption and least privilege reduce what should happen; operations teams still need evidence about what did happen, while applications need row-by-row enforcement and controlled presentation of sensitive fields. ServiceHub will combine four different tools without pretending they are interchangeable: SQL Server Audit for security/compliance records, Extended Events for diagnostic telemetry, Row-Level Security (RLS) for database-enforced row filtering/blocking, and Dynamic Data Masking (DDM) for controlled display.
The chapter closes with hardening discipline: protect the audit target, patch the engine and drivers, reduce attack surface, test policies under impersonated users, and assume highly privileged administrators can change controls unless evidence is exported/protected outside their administrative boundary.
Distinguish SQL Server Audit, Extended Events, error logs, RLS and DDM by purpose and trust boundary.
Implement and test a session-context RLS filter/block policy with multi-tenant ServiceHub rows.
Demonstrate DDM output and why it is not authorization or encryption.
Inventory Audit/XEvent state and design protected audit-target retention.
Build a practical security-hardening and incident-verification checklist.
1. Pick the control whose semantics match the requirement
SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('ProductUpdateLevel') AS update_level, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('EngineEdition') AS engine_edition, SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN (N'contained database authentication', N'column encryption enclave type', N'clr enabled',N'xp_cmdshell');GOSELECT DB_NAME() AS database_name, ORIGINAL_LOGIN() AS original_login, SUSER_SNAME() AS execution_login, USER_NAME() AS database_user;GOUSE master;GOIF DB_ID(N'ServiceHubSecurityLab') IS NULL CREATE DATABASE ServiceHubSecurityLab;GOALTER DATABASE ServiceHubSecurityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubSecurityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'sec') IS NULL EXEC(N'CREATE SCHEMA sec AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab14') IS NULL EXEC(N'CREATE SCHEMA lab14 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrderSecure',N'U') IS NULLBEGIN CREATE TABLE ops.WorkOrderSecure ( work_order_id bigint IDENTITY(14001,1) NOT NULL CONSTRAINT PK_ops_WorkOrderSecure PRIMARY KEY, tenant_code char(3) NOT NULL, customer_code varchar(16) NOT NULL, customer_name nvarchar(100) NOT NULL, customer_email varchar(200) NOT NULL, status varchar(16) NOT NULL, priority tinyint NOT NULL, amount decimal(12,2) NOT NULL, opened_at datetime2(0) NOT NULL, notes nvarchar(400) NULL, CONSTRAINT CK_ops_WorkOrderSecure_tenant CHECK (tenant_code IN ('N01','W02','E03')), CONSTRAINT CK_ops_WorkOrderSecure_status CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')), CONSTRAINT CK_ops_WorkOrderSecure_priority CHECK (priority BETWEEN 1 AND 5) ); INSERT ops.WorkOrderSecure (tenant_code,customer_code,customer_name,customer_email,status,priority,amount,opened_at,notes) VALUES ('N01','CUST-1401',N'Mina Rahimi','mina@example.test','OPEN',2,240.00,'2026-08-01T09:00:00',N'North queue'), ('N01','CUST-1402',N'Owen Brooks','owen@example.test','ASSIGNED',3,480.00,'2026-08-01T10:15:00',N'North dispatch'), ('W02','CUST-1403',N'Sara Chen','sara@example.test','ESCALATED',5,860.00,'2026-08-01T11:30:00',N'West escalation'), ('W02','CUST-1404',N'Ali Reza','ali@example.test','OPEN',1,120.00,'2026-08-02T08:20:00',N'West queue'), ('E03','CUST-1405',N'Nora Bell','nora@example.test','CLOSED',4,735.00,'2026-08-02T09:40:00',N'East closed'), ('E03','CUST-1406',N'David Kim','david@example.test','OPEN',2,315.00,'2026-08-02T12:05:00',N'East queue'); CREATE INDEX IX_ops_WorkOrderSecure_tenant_status ON ops.WorkOrderSecure(tenant_code,status,opened_at) INCLUDE(customer_code,priority,amount);END;GO| Control | Primary job | What it does not guarantee |
|---|---|---|
| SQL Server Audit | Durable security/compliance-oriented event capture to an audit target | Does not prevent the action; privileged administrators can manage/tamper with audit configuration unless governance protects the boundary. |
| Extended Events | Focused diagnostic/event telemetry with rich filtering and low-overhead designs | Not automatically a compliance archive; events can expose sensitive SQL and retention depends on target configuration. |
| SQL error log / login auditing | Operational server/login failure evidence | Not a complete object/data-access audit trail. |
| Row-Level Security | Database-enforced filter/block predicates per row | Does not encrypt rows; privileged principals that can alter policy remain a governance risk. |
| Dynamic Data Masking | Obscure returned values from users without UNMASK | Does not change stored data, prevent inference, or replace SELECT authorization. |
2. Row-Level Security makes tenant filtering part of database execution
RLS uses an inline table-valued function as a security predicate
and binds it to a target table through a security policy. A
filter predicate hides rows from reads and eligible
modifications; block predicates reject writes that violate the
rule. The application can pass a trusted tenant identifier in
SESSION_CONTEXT, but the application must establish
that value from authenticated identity—not from an untrusted
request parameter.
USE ServiceHubSecurityLab;GOIF USER_ID(N'lab14_rls_app') IS NULL CREATE USER lab14_rls_app WITHOUT LOGIN;GRANT SELECT,INSERT,UPDATE ON OBJECT::ops.WorkOrderSecure TO lab14_rls_app;GOCREATE OR ALTER FUNCTION sec.fn_tenant_access(@tenant_code char(3))RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT 1 AS allowed WHERE @tenant_code=CONVERT(char(3),SESSION_CONTEXT(N'tenant_code'));GOIF EXISTS (SELECT 1 FROM sys.security_policies WHERE name=N'WorkOrderTenantPolicy') DROP SECURITY POLICY sec.WorkOrderTenantPolicy;GOCREATE SECURITY POLICY sec.WorkOrderTenantPolicy ADD FILTER PREDICATE sec.fn_tenant_access(tenant_code) ON ops.WorkOrderSecure, ADD BLOCK PREDICATE sec.fn_tenant_access(tenant_code) ON ops.WorkOrderSecure AFTER INSERT, ADD BLOCK PREDICATE sec.fn_tenant_access(tenant_code) ON ops.WorkOrderSecure AFTER UPDATEWITH (STATE=ON,SCHEMABINDING=ON);GOEXECUTE AS USER=N'lab14_rls_app';EXEC sys.sp_set_session_context @key=N'tenant_code',@value=N'N01';SELECT tenant_code,work_order_id,customer_code,statusFROM ops.WorkOrderSecureORDER BY work_order_id; -- only N01 rowsGOBEGIN TRY INSERT ops.WorkOrderSecure (tenant_code,customer_code,customer_name,customer_email,status,priority,amount,opened_at,notes) VALUES ('W02','CUST-BLOCK',N'Blocked tenant','blocked@example.test','OPEN',1,50,SYSUTCDATETIME(),N'should fail');END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS blocked_error,ERROR_MESSAGE() AS blocked_message;END CATCH;GOREVERT;
RLS applies even to dbo/owners when the policy is
enabled, although sufficiently privileged users can alter or
disable the policy. That is why policy DDL and audit evidence
matter. Review predicate performance too: a security predicate
participates in every affected query and can change plans.
3. Dynamic Data Masking changes result presentation, not stored values
USE ServiceHubSecurityLab;GODROP TABLE IF EXISTS lab14.MaskedContact;CREATE TABLE lab14.MaskedContact( contact_id int IDENTITY(1,1) PRIMARY KEY, display_name nvarchar(100) NOT NULL, email varchar(200) MASKED WITH (FUNCTION='email()') NOT NULL, phone varchar(30) MASKED WITH (FUNCTION='partial(0,"XXX-XXX-",4)') NOT NULL, annual_value decimal(12,2) MASKED WITH (FUNCTION='default()') NOT NULL);INSERT lab14.MaskedContact(display_name,email,phone,annual_value)VALUES (N'Mina Rahimi','mina@example.test','555-010-1401',125000), (N'Owen Brooks','owen@example.test','555-010-1402',83000);GOIF USER_ID(N'lab14_masked_reader') IS NULL CREATE USER lab14_masked_reader WITHOUT LOGIN;GRANT SELECT ON OBJECT::lab14.MaskedContact TO lab14_masked_reader;GOEXECUTE AS USER=N'lab14_masked_reader';SELECT * FROM lab14.MaskedContact;REVERT;GOGRANT UNMASK ON OBJECT::lab14.MaskedContact TO lab14_masked_reader;EXECUTE AS USER=N'lab14_masked_reader';SELECT * FROM lab14.MaskedContact; -- original values now visibleREVERT;REVOKE UNMASK ON OBJECT::lab14.MaskedContact FROM lab14_masked_reader;GO
The first query should return masked presentations; the second
should expose original values because UNMASK was
granted. The stored rows never changed. Users with broad control
permissions can see unmasked values, and users with ad-hoc query
access can sometimes infer masked values with predicates.
Microsoft explicitly documents inference/brute-force techniques against masked columns. DDM is useful for accidental-exposure reduction and application presentation; it is not a security boundary against a malicious user who has broad query access. Use permissions, RLS and appropriate encryption for that job.
4. SQL Server Audit is a security record; Extended Events is diagnostic instrumentation
SELECT name,audit_guid,type_desc,on_failure_desc,queue_delay,is_state_enabledFROM sys.server_auditsORDER BY name;GOUSE ServiceHubSecurityLab;SELECT name,is_state_enabled,audit_guid,create_date,modify_dateFROM sys.database_audit_specificationsORDER BY name;GOSELECT name,startup_state,event_retention_mode_desc, max_memory,max_dispatch_latencyFROM sys.server_event_sessionsORDER BY name;GOA SQL Server Audit object owns an audit target; server or database audit specifications select action groups/actions to record. Targets can include binary audit files and, on Windows, event logs. Audit targets need ACLs, capacity, retention and off-host protection appropriate to the threat model. A principal who can alter Audit configuration is inside that trust boundary unless external monitoring detects changes.
-- Administrative template. FILEPATH must already exist and be writable by-- the SQL Server service account, while unauthorized users cannot modify it.-- USE master;-- CREATE SERVER AUDIT ServiceHubSecurityAudit-- TO FILE (FILEPATH=N'<protected audit directory>',MAXSIZE=512 MB,MAX_ROLLOVER_FILES=20)-- WITH (QUEUE_DELAY=1000,ON_FAILURE=FAIL_OPERATION);-- ALTER SERVER AUDIT ServiceHubSecurityAudit WITH (STATE=ON);-- GO-- USE ServiceHubSecurityLab;-- CREATE DATABASE AUDIT SPECIFICATION ServiceHubDataAccessSpec-- FOR SERVER AUDIT ServiceHubSecurityAudit-- ADD (SELECT ON OBJECT::ops.WorkOrderSecure BY public),-- ADD (DATABASE_OBJECT_CHANGE_GROUP)-- WITH (STATE=ON);
ON_FAILURE is a business availability decision.
CONTINUE can preserve service while losing required
audit records; FAIL_OPERATION can preserve audit
completeness while denying audited operations when the target is
unavailable. Do not copy a compliance setting without agreeing
which failure mode the organization accepts.
Extended Events is excellent for focused troubleshooting—login errors, permission failures, query context, deadlocks or security DDL—but session predicates and actions can capture sensitive statements and parameters. Keep collection narrow and protect XEL files like other sensitive telemetry.
5. Harden the instance around the controls, not just inside the database
Security is a lifecycle: current patches, supported drivers, least-privileged service accounts, restricted network listeners/firewalls, TLS validation, disabled unused features, reviewed logins/roles, protected backups/keys, controlled SQL Agent proxies, auditable policy changes and tested recovery. A secure database on an unpatched host with reusable administrator credentials is not secure.
SELECT SERVERPROPERTY('ProductVersion') AS product_version, SERVERPROPERTY('ProductUpdateLevel') AS update_level, SERVERPROPERTY('Edition') AS edition, SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_auth_only;GOSELECT name,value_in_useFROM sys.configurationsWHERE name IN (N'xp_cmdshell',N'clr enabled',N'contained database authentication', N'column encryption enclave type',N'remote access')ORDER BY name;GOSELECT name,type_desc,is_disabled,create_date,modify_dateFROM sys.server_principalsWHERE type IN ('S','U','G','E','X') AND name NOT LIKE N'##%'ORDER BY is_disabled,name;GOThis snapshot is not a universal “secure if zero” checklist. For example, CLR can be justified, containment can be intentional, and remote administration requirements differ. The point is to make every enabled surface attributable to an owner and business requirement, then monitor drift.
6. Production judgment
Production judgment. Export or forward critical audit records so the people being audited cannot silently destroy the only copy. Test RLS as multiple identities and validate predicate performance. Treat DDM as presentation only. Preserve Extended Events with appropriate access controls and retention. Patch SQL Server and drivers on a defined cadence, re-check Microsoft security advisories and current CU/GDR branches, and rehearse key/audit recovery as part of disaster recovery.
Chapter cleanup and security-policy verification
USE ServiceHubSecurityLab;GOIF EXISTS (SELECT 1 FROM sys.security_policies WHERE name=N'WorkOrderTenantPolicy') DROP SECURITY POLICY sec.WorkOrderTenantPolicy;DROP FUNCTION IF EXISTS sec.fn_tenant_access;DROP TABLE IF EXISTS lab14.MaskedContact;IF USER_ID(N'lab14_rls_app') IS NOT NULL DROP USER lab14_rls_app;IF USER_ID(N'lab14_masked_reader') IS NOT NULL DROP USER lab14_masked_reader;GOSELECT name,type_desc FROM sys.database_principals WHERE name LIKE N'lab14_%';SELECT name FROM sys.security_policies WHERE name LIKE N'%ServiceHub%' OR name LIKE N'WorkOrder%';GO-- Optional final cleanup after the chapter:-- USE master;-- ALTER DATABASE ServiceHubSecurityLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;-- DROP DATABASE ServiceHubSecurityLab;Check your understanding
- What is the main semantic difference between SQL Server Audit and Extended Events?
- How does an RLS filter predicate differ from a block predicate?
- Why must the application protect the tenant value placed in SESSION_CONTEXT?
- Why does DDM not replace SELECT permission or encryption?
- Why should important audit records be protected outside the routine SQL Server administrator boundary?
Review the answers
1. Audit is designed for security/compliance event recording through audit specifications and protected targets; Extended Events is general-purpose diagnostic telemetry whose scope/retention must be designed explicitly.
2. A filter predicate silently restricts which rows are visible/eligible to reads and certain modifications; a block predicate rejects writes that would violate the policy.
3. If a user can choose another tenant identifier freely, the predicate faithfully enforces the attacker-supplied value. The session context must be derived from authenticated/authorized application identity.
4. DDM only changes returned presentation for users lacking UNMASK. Stored values remain unchanged, privileged users can see them, and ad-hoc predicates can infer data.
5. Highly privileged SQL Server principals can alter audit configuration. External/independently protected retention makes tampering detectable and preserves evidence during compromise.
Authoritative references
- SQL Server Audit — server/database auditing concepts
- SQL Server Audit action groups — auditable action groups and events
- Create server/database audit specification — Audit target/specification workflow
- Row-Level Security — filter/block predicates and security-policy behavior
- CREATE SECURITY POLICY — RLS policy syntax and permissions
- Dynamic Data Masking — masking semantics, UNMASK and inference caveat
- SQL Server security center — security control map
- SQL Server security best practices — hardening and layered security