Security

Encryption hierarchy: Service Master Key → Database Master Key → Certificates / Asymmetric keys → Symmetric keys → Data.

41.1 Security Hierarchy

Windows / Azure AD (Entra ID)
   └── SQL Server Instance  ← Logins, Server Roles, Server Permissions
         └── Database        ← Users, Database Roles, Database Permissions
               └── Schema     ← Schema Permissions
                     └── Objects (tables, views, procs) ← Object Permissions

41.2 Authentication

Windows authentication (Kerberos/NTLM). Recommended.

SQL Server authentication (username and password). Enforce password policy.

Microsoft Entra ID (Azure AD) authentication: Azure SQL and SQL Server 2022+ (Arc-enabled).

Contained database users: authenticate at the database level.

41.3 Logins and Users

CREATE LOGIN AppLogin WITH PASSWORD = '<StrongP@ssw0rd!>', CHECK_POLICY = ON, CHECK_EXPIRATION = ON,
     DEFAULT_DATABASE = ShopDB;
CREATE LOGIN [DOMAIN\Asha] FROM WINDOWS;
CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER;   -- Entra ID

USE ShopDB;
CREATE USER AppUser FOR LOGIN AppLogin WITH DEFAULT_SCHEMA = Sales;
CREATE USER ContainedUser WITH PASSWORD = '<StrongP@ssw0rd!>';  -- contained database
CREATE USER NoLoginUser WITHOUT LOGIN;                          -- for EXECUTE AS / signing

ALTER LOGIN AppLogin DISABLE;
ALTER LOGIN sa DISABLE;              -- security best practice
-- Orphaned users after a restore:
ALTER USER AppUser WITH LOGIN = AppLogin;

41.4 Roles

Fixed server roles: sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator, public. 2022 adds granular ##MS_*## roles (e.g., ##MS_ServerStateReader##).

Fixed database roles: db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader.

User-defined roles and application roles.

CREATE ROLE SalesReader;
ALTER ROLE SalesReader ADD MEMBER AppUser;
ALTER SERVER ROLE dbcreator ADD MEMBER AppLogin;
EXEC sp_setapprole 'AppRole', 'password';

41.5 Permissions (DCL)

GRANT SELECT ON SCHEMA::Sales TO SalesReader;
GRANT SELECT, INSERT, UPDATE ON dbo.Orders TO AppUser;
GRANT EXECUTE ON dbo.usp_CreateOrder TO AppUser;
GRANT SELECT ON dbo.Customers (CustomerID, FirstName) TO Analyst;   -- column-level
DENY  DELETE ON dbo.Orders TO AppUser;     -- DENY overrides GRANT
REVOKE INSERT ON dbo.Orders FROM AppUser;  -- removes a GRANT or DENY
GRANT SELECT ON dbo.Orders TO TeamLead WITH GRANT OPTION;
GRANT VIEW SERVER STATE TO MonitorLogin;
GRANT VIEW DATABASE PERFORMANCE STATE TO DevUser;  -- 2022

-- Check effective permissions
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');
EXECUTE AS USER = 'AppUser'; SELECT * FROM fn_my_permissions(NULL, 'DATABASE'); REVERT;
SELECT * FROM sys.database_permissions;
SELECT * FROM sys.server_permissions;

41.6 Ownership Chaining and Module Signing

Ownership chaining: if a procedure and its tables share an owner, the caller needs only EXECUTE.

Cross-database ownership chaining (keep it disabled).

Module signing with certificates gives elevated permissions safely.

EXECUTE AS impersonation and the TRUSTWORTHY property (keep it OFF).

41.7 Row-Level Security (RLS, 2016+)

CREATE FUNCTION Security.fn_TenantFilter (@TenantID INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS INT);
GO
CREATE SECURITY POLICY Security.TenantPolicy
ADD FILTER PREDICATE Security.fn_TenantFilter(TenantID) ON dbo.Orders,
ADD BLOCK  PREDICATE Security.fn_TenantFilter(TenantID) ON dbo.Orders AFTER INSERT
WITH (STATE = ON);

EXEC sp_set_session_context @key = N'TenantID', @value = 5;

41.8 Dynamic Data Masking (DDM)

DDM is obfuscation, not encryption. Inference attacks are possible.

ALTER TABLE dbo.Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Customers ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)');
-- Functions: default(), email(), random(start,end), partial(prefix,padding,suffix), datetime('Y')
GRANT UNMASK TO Auditor;   -- granular UNMASK at the column/table/schema level since 2022

41.9 Encryption

TDE (Transparent Data Encryption) Data at rest (files, backups)

Encrypts pages on write. Transparent to applications.

Always Encrypted Data in use, from DBAs and admins Client-side encryption. Deterministic vs randomized. Secure enclaves enable rich queries.

Column-level encryption ENCRYPTBYKEY / DECRYPTBYKEY with symmetric keys Backup encryption

WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE =...) Data in transit

Force encryption. TLS 1.3 / TDS 8.0 (strict) in 2022.

Encryption hierarchy: Service Master Key → Database Master Key → Certificates / Asymmetric keys → Symmetric keys → Data.

-- TDE setup
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<StrongP@ss>';
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDECert TO FILE = 'C:\Keys\TDECert.cer'
    WITH PRIVATE KEY (FILE = 'C:\Keys\TDECert.pvk', ENCRYPTION BY PASSWORD = '<StrongP@ss>');  -- CRITICAL!
USE ShopDB;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE ShopDB SET ENCRYPTION ON;
SELECT * FROM sys.dm_database_encryption_keys;

41.10 Auditing

Other options: C2 audit (legacy), Common Criteria compliance, login auditing, SQL Server Ledger.

CREATE SERVER AUDIT ShopAudit TO FILE (FILEPATH = 'E:\Audit\', MAXSIZE = 100 MB);
ALTER SERVER AUDIT ShopAudit WITH (STATE = ON);
CREATE DATABASE AUDIT SPECIFICATION ShopDBAudit FOR SERVER AUDIT ShopAudit
ADD (SELECT, UPDATE, DELETE ON dbo.Customers BY public),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
SELECT * FROM sys.fn_get_audit_file('E:\Audit\*.sqlaudit', DEFAULT, DEFAULT);

41.11 Security Best Practices Checklist

✅ Principle of least privilege. Grant through roles, not individual users.

✅ Disable or rename sa. Prefer Windows or Entra authentication.

✅ Use parameterized queries to prevent SQL injection.

✅ Encrypt connections, backups, and sensitive columns.

✅ Keep patches current (CUs and GDRs).

✅ Surface area reduction: disable xp_cmdshell, CLR, OLE Automation, and Ad Hoc Distributed Queries when they're not needed.

✅ Run SQL Server Vulnerability Assessment and Data Discovery & Classification.

✅ Use a separate service account per service (gMSA).

✅ Audit privileged activity.