Chapter 14 · Security: Logins, Users, Roles, Permissions, Encryption, and Auditing
Server/Database Roles, GRANT/DENY/REVOKE, Ownership Chains, EXECUTE AS, and Least Privilege
Design least-privilege authorization with roles, permission hierarchy, GRANT/DENY/REVOKE, ownership chains, execution context and module-signing boundaries.
Learning outcomes
Authentication gets a ServiceHub identity through the door;
authorization decides which doors exist afterward. The dangerous
shortcut is to treat roles as labels rather than security
tokens, or to answer every permission error with
db_owner. SQL Server instead provides a hierarchy
of securables, principals, explicit
grants/denies, role membership, ownership chains,
execution-context controls and certificate signing.
The production skill is not memorizing every permission name. It is designing the smallest permission set that performs one job, making effective access observable, and retaining a rollback path when application modules or ownership boundaries change.
Apply GRANT, DENY and REVOKE at appropriate server/database/schema/object scopes.
Use user-defined roles to model tasks instead of granting broad fixed-role membership.
Explain DENY precedence including the historical column-GRANT exception.
Observe same-owner ownership chaining and recognize where dynamic SQL breaks it.
Choose deliberately among caller context, EXECUTE AS and certificate signing for privileged modules.
1. Think principal → securable → permission → scope
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
A principal is the identity receiving rights. A securable is the
protected entity: server, database, schema, object, endpoint and
so on. A permission is an operation such as SELECT,
EXECUTE, ALTER or
CONTROL. Higher-scope permissions can imply
permissions lower in the hierarchy. That makes schema-level
grants useful, but also makes overly broad grants more powerful
than they look.
USE ServiceHubSecurityLab;GOIF USER_ID(N'lab14_dispatch_user') IS NULL CREATE USER lab14_dispatch_user WITHOUT LOGIN;IF USER_ID(N'lab14_support_user') IS NULL CREATE USER lab14_support_user WITHOUT LOGIN;IF DATABASE_PRINCIPAL_ID(N'lab14_dispatch_role') IS NULL CREATE ROLE lab14_dispatch_role AUTHORIZATION dbo;IF DATABASE_PRINCIPAL_ID(N'lab14_support_role') IS NULL CREATE ROLE lab14_support_role AUTHORIZATION dbo;GOGRANT SELECT ON SCHEMA::ops TO lab14_support_role;GRANT SELECT,UPDATE ON OBJECT::ops.WorkOrderSecure TO lab14_dispatch_role;DENY DELETE ON OBJECT::ops.WorkOrderSecure TO lab14_dispatch_role;ALTER ROLE lab14_support_role ADD MEMBER lab14_support_user;ALTER ROLE lab14_dispatch_role ADD MEMBER lab14_dispatch_user;GOSELECT pr.name AS principal_name,pe.state_desc,pe.permission_name, pe.class_desc,OBJECT_SCHEMA_NAME(pe.major_id) AS object_schema, OBJECT_NAME(pe.major_id) AS object_nameFROM sys.database_permissions AS peJOIN sys.database_principals AS pr ON pr.principal_id=pe.grantee_principal_idWHERE pr.name LIKE N'lab14_%'ORDER BY pr.name,pe.class_desc,pe.permission_name;GOEXECUTE AS USER=N'lab14_dispatch_user';SELECT HAS_PERMS_BY_NAME(N'ops.WorkOrderSecure',N'OBJECT',N'SELECT') AS can_select, HAS_PERMS_BY_NAME(N'ops.WorkOrderSecure',N'OBJECT',N'UPDATE') AS can_update, HAS_PERMS_BY_NAME(N'ops.WorkOrderSecure',N'OBJECT',N'DELETE') AS can_delete;REVERT;GO
HAS_PERMS_BY_NAME evaluates one permission in the
current execution context. It is useful evidence, not a complete
entitlement report: ownership, role membership, impersonation,
signatures and server permissions can all contribute to an
effective token.
2. GRANT adds, DENY blocks inheritance, REVOKE removes a statement
GRANT gives a permission.
DENY prevents the principal from receiving that
permission through role or group membership.
REVOKE removes an explicit GRANT or DENY at that
scope; it does not mean “deny.” A higher-scope permission can
become effective again after a lower explicit grant is revoked.
USE ServiceHubSecurityLab;GO-- support_role grants SELECT on the entire ops schema.GRANT SELECT ON SCHEMA::ops TO lab14_support_role;ALTER ROLE lab14_support_role ADD MEMBER lab14_support_user;EXECUTE AS USER=N'lab14_support_user';SELECT HAS_PERMS_BY_NAME(N'ops.WorkOrderSecure',N'OBJECT',N'SELECT') AS before_deny;REVERT;GODENY SELECT ON OBJECT::ops.WorkOrderSecure TO lab14_support_user;EXECUTE AS USER=N'lab14_support_user';SELECT HAS_PERMS_BY_NAME(N'ops.WorkOrderSecure',N'OBJECT',N'SELECT') AS after_deny;REVERT;GOREVOKE SELECT ON OBJECT::ops.WorkOrderSecure FROM lab14_support_user;EXECUTE AS USER=N'lab14_support_user';SELECT HAS_PERMS_BY_NAME(N'ops.WorkOrderSecure',N'OBJECT',N'SELECT') AS after_revoke;REVERT;GO
Microsoft documents a backward-compatibility inconsistency: a
table-level DENY does not take precedence over a
column-level GRANT. Do not design a
least-privilege system that depends on people remembering this
exception. Prefer clear scopes and test the effective token.
3. Same-owner ownership chains can expose an operation without exposing the table
When a module and a referenced object share the same owner, SQL Server can avoid rechecking the caller’s permission on the referenced object after the caller has permission to execute the module. This lets a procedure become a narrow API surface. The chain is about ownership and static object references, not about “stored procedures are trusted.”
USE ServiceHubSecurityLab;GOCREATE OR ALTER PROCEDURE api.GetOpenWorkOrders @tenant_code char(3)ASBEGIN SET NOCOUNT ON; SELECT work_order_id,tenant_code,customer_code,status,priority,amount,opened_at FROM ops.WorkOrderSecure WHERE tenant_code=@tenant_code AND status='OPEN' ORDER BY opened_at,work_order_id;END;GOGRANT EXECUTE ON OBJECT::api.GetOpenWorkOrders TO lab14_dispatch_user;DENY SELECT ON OBJECT::ops.WorkOrderSecure TO lab14_dispatch_user;GOEXECUTE AS USER=N'lab14_dispatch_user';BEGIN TRY SELECT TOP (1) * FROM ops.WorkOrderSecure;END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS direct_error,ERROR_MESSAGE() AS direct_message;END CATCH;EXEC api.GetOpenWorkOrders @tenant_code='N01';REVERT;GOExpected result: direct table access fails, while the same-owner stored procedure can return the rows. The permission boundary is the procedure contract. If the table or module owner changes, or if the code crosses database/server boundaries, re-evaluate the chain instead of assuming old behavior persists.
4. Dynamic SQL breaks the ordinary ownership-chain shortcut
Dynamic SQL is parsed and permission-checked at execution time. A procedure that builds a string referencing a table does not get the same static ownership-chain behavior for that reference. This is one reason Chapter 13 treated dynamic SQL as a security/deployment boundary.
USE ServiceHubSecurityLab;GOCREATE OR ALTER PROCEDURE api.CountOrdersDynamicASBEGIN SET NOCOUNT ON; EXEC sys.sp_executesql N'SELECT COUNT_BIG(*) AS order_count FROM ops.WorkOrderSecure;';END;GOGRANT EXECUTE ON OBJECT::api.CountOrdersDynamic TO lab14_dispatch_user;GOEXECUTE AS USER=N'lab14_dispatch_user';BEGIN TRY EXEC api.CountOrdersDynamic; END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS dynamic_error,ERROR_MESSAGE() AS dynamic_message; END CATCH;REVERT;GOIF USER_ID(N'lab14_module_executor') IS NULL CREATE USER lab14_module_executor WITHOUT LOGIN;GRANT SELECT ON OBJECT::ops.WorkOrderSecure TO lab14_module_executor;GOCREATE OR ALTER PROCEDURE api.CountOrdersDynamicWITH EXECUTE AS 'lab14_module_executor'ASBEGIN SET NOCOUNT ON; EXEC sys.sp_executesql N'SELECT COUNT_BIG(*) AS order_count FROM ops.WorkOrderSecure;';END;GOEXECUTE AS USER=N'lab14_dispatch_user';EXEC api.CountOrdersDynamic;REVERT;GO
EXECUTE AS changes the execution context for the
module. Choose a dedicated least-privileged principal, not
dbo, unless full database-owner authority is truly
required. Stand-alone EXECUTE AS must be paired
with REVERT; connection pooling makes forgotten
execution context especially dangerous.
5. Certificate signing can add permission without replacing caller identity
Module signing is another pattern when a module needs privileges its callers should not have. A certificate user receives the required permission, the module is signed, and that certificate-derived permission is added while the original caller remains visible for auditing. This can be preferable to impersonation, especially for cross-database or server-scoped administrative operations, but it introduces certificate lifecycle and deployment responsibilities.
A real signing workflow creates certificates/private keys and must define how they are backed up and reproduced in every environment. The technique is demonstrated conceptually here; do not paste a course-wide certificate password into production. Chapter 14’s encryption lesson develops the key-lifecycle discipline needed before deploying signed modules broadly.
USE ServiceHubSecurityLab;GOEXECUTE AS USER=N'lab14_dispatch_user';SELECT ORIGINAL_LOGIN() AS original_login, SUSER_SNAME() AS execution_login, USER_NAME() AS execution_user, IS_ROLEMEMBER(N'lab14_dispatch_role') AS dispatch_role_member, IS_ROLEMEMBER(N'db_owner') AS db_owner_member;REVERT;GOdb_owner.
db_owner can perform essentially all
configuration and maintenance activity in the database,
including changing security policy. That is not a
permission-error fix; it is removal of the authorization
boundary. Grant the task to a user-defined role or expose a
carefully designed module instead.
6. Production judgment
Production judgment. Review explicit
permissions and role memberships as code. Treat
DENY as a deliberate control, not as cleanup for
messy grants. Document module owners, execution contexts,
certificate signatures and cross-database dependencies. Retest
authorization after schema ownership, database owner,
restore/migration, contained-user, or module changes.
Lab cleanup and verification
USE ServiceHubSecurityLab;GODROP PROCEDURE IF EXISTS api.CountOrdersDynamic;DROP PROCEDURE IF EXISTS api.GetOpenWorkOrders;IF USER_ID(N'lab14_module_executor') IS NOT NULL DROP USER lab14_module_executor;IF DATABASE_PRINCIPAL_ID(N'lab14_dispatch_role') IS NOT NULLBEGIN ALTER ROLE lab14_dispatch_role DROP MEMBER lab14_dispatch_user; DROP ROLE lab14_dispatch_role;END;IF DATABASE_PRINCIPAL_ID(N'lab14_support_role') IS NOT NULLBEGIN ALTER ROLE lab14_support_role DROP MEMBER lab14_support_user; DROP ROLE lab14_support_role;END;IF USER_ID(N'lab14_dispatch_user') IS NOT NULL DROP USER lab14_dispatch_user;IF USER_ID(N'lab14_support_user') IS NOT NULL DROP USER lab14_support_user;GOCheck your understanding
- What is the semantic difference between REVOKE and DENY?
- Why can a caller execute a same-owner procedure even when direct SELECT is denied?
- What changes when the procedure uses dynamic SQL to reference the table?
- Why is EXECUTE AS OWNER often broader than necessary?
- What advantage can certificate signing have over impersonation?
Review the answers
1. REVOKE removes an explicit permission statement at that scope; DENY actively prevents the permission from being inherited through memberships, subject to documented exceptions.
2. After EXECUTE permission is checked, same-owner ownership chaining can skip a separate caller permission check on the statically referenced table.
3. The runtime string is permission-checked in its execution context and does not receive the ordinary static ownership-chain shortcut for the dynamically referenced object.
4. If the owner resolves to dbo, the module can gain database-owner authority rather than the small capability the operation actually needs.
5. Signing can add narrowly granted certificate-user permissions to a module while preserving the original caller identity for audit and avoiding a broad impersonated context.
Authoritative references
- Permissions hierarchy — securables and hierarchical permissions
- DENY — DENY semantics and the column-GRANT exception
- REVOKE — removing permission statements
- EXECUTE AS clause — module execution context and least-privilege guidance
- EXECUTE AS statement — session context switching
- REVERT — restoring impersonated execution context
- Signing stored procedures with a certificate — certificate-based module permission pattern
- SQL Server security best practices — least privilege and role design