Database security layers authentication (who), authorization (what), encryption (data protection), and auditing (accountability). No single control suffices — defense in depth is required for production and compliance (SOC 2, HIPAA, PCI-DSS).

Authentication Modes 

  -- Check current mode
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_only;
-- 0 = Mixed Mode, 1 = Windows only
Mode Use Case
Windows Authentication Domain-joined apps, SSPI, no password in connection string
SQL Server Authentication Cross-platform apps, contained users, legacy
Azure AD Azure SQL, managed identity

Enable Mixed Mode only when required — Windows auth reduces credential sprawl.

Logins and Users 

  -- Server login
CREATE LOGIN app_api WITH PASSWORD = N'ComplexP@ssw0rd!2024' MUST_CHANGE, CHECK_POLICY = ON;
GO

USE MyApp;
CREATE USER app_api FOR LOGIN app_api;
ALTER ROLE db_datareader ADD MEMBER app_api;
ALTER ROLE db_datawriter ADD MEMBER app_api;
GRANT EXECUTE ON SCHEMA::dbo TO app_api;

Separate logins per application — never share sa or dbo across services.

Contained Database Users (No Server Login) 

  CREATE DATABASE MyApp CONTAINMENT = PARTIAL;
USE MyApp;
CREATE USER app_report WITH PASSWORD = N'StrongReportP@ss1';
ALTER ROLE db_datareader ADD MEMBER app_report;

Simplifies AG failover — users travel with database.

Least Privilege 

  -- Custom role
CREATE ROLE app_readonly;
GRANT SELECT ON SCHEMA::dbo TO app_readonly;
ALTER ROLE app_readonly ADD MEMBER analyst_jane;

-- Explicit deny overrides grant
DENY DELETE ON dbo.Orders TO app_api;
DENY ALTER ON SCHEMA::dbo TO app_api;

Review permissions regularly:

  SELECT dp.name AS principal, dp.type_desc, o.name AS object_name, p.permission_name, p.state_desc
FROM sys.database_permissions p
INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE dp.name = N'app_api';

Server Roles — Avoid Over-Assignment

Role Risk
sysadmin Full control — break-glass only
db_owner Full database control
securityadmin Can escalate privileges

Grant minimum fixed/database roles; prefer schema-level grants.

Transparent Data Encryption (TDE)

Encrypts data and log files at rest — transparent to applications:

  USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'MasterKeyStr0ng!';

CREATE CERTIFICATE TDE_Cert WITH SUBJECT = N'MyApp TDE Certificate';

USE MyApp;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

ALTER DATABASE MyApp SET ENCRYPTION ON;

SELECT db.name, dek.encryption_state
FROM sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dek ON db.database_id = dek.database_id
WHERE db.name = N'MyApp';

Backup certificates — without cert + key, encrypted backups are unrecoverable.

Always Encrypted

Client-side encryption — SQL Server never sees plaintext for protected columns:

  -- Requires Column Master Key and Column Encryption Key setup in SSMS wizard
CREATE TABLE dbo.Patients (
    PatientID INT PRIMARY KEY,
    Name NVARCHAR(100),
    SSN NVARCHAR(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = CEK_Auto1,
            ENCRYPTION_TYPE = DETERMINISTIC,
            ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256'
        )
);

Application driver must support Always Encrypted — deterministic allows equality search; randomized is more secure.

Dynamic Data Masking 

  ALTER TABLE dbo.Users
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE dbo.Users
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

-- Grant UNMASK for privileged roles only
GRANT UNMASK TO hr_admin;

Masking is not encryption — privileged users see full data.

Row-Level Security 

  CREATE FUNCTION dbo.fn_SecurityPredicate(@TenantID INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS fn_result
WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS INT);

CREATE SECURITY POLICY dbo.TenantFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(TenantID) ON dbo.Orders
WITH (STATE = ON);

App sets SESSION_CONTEXT after login — multi-tenant isolation in database.

SQL Server Audit 

  CREATE SERVER AUDIT ProductionAudit
TO FILE (FILEPATH = N'D:\Audit\', MAXSIZE = 100 MB, MAX_ROLLOVER_FILES = 20);

CREATE SERVER AUDIT SPECIFICATION ProductionAuditSpec
FOR SERVER AUDIT ProductionAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_CHANGE_GROUP);

ALTER SERVER AUDIT ProductionAudit WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION ProductionAuditSpec WITH (STATE = ON);

Database-level audit for sensitive tables:

  CREATE DATABASE AUDIT SPECIFICATION MyAppAuditSpec
FOR SERVER AUDIT ProductionAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.PaymentCards BY public);

Forward audit logs to SIEM (Splunk, Azure Monitor) for alerting.

Common Mistakes

Mistake Risk
sa in connection strings Total compromise
Weak passwords, no policy Brute force
Excessive sysadmin membership Insider threat, malware spread
TDE without cert backup Unrecoverable backups
Auditing disabled to save disk Compliance failure after incident
Trusting masking as encryption Data leak via privileged accounts

Performance Tips

  • TDE adds 3–5% CPU — negligible vs compliance requirement
  • Audit selective events — full DML audit on hot tables adds I/O
  • Index RLS predicate columns — filter must be sargable
  • Use Windows auth — eliminates SQL password hash attacks

Production Scenario: PCI Scope Reduction

  1. Store card tokens only — no PAN in database
  2. Always Encrypted or external vault for any sensitive fields
  3. Separate network segment for SQL Server
  4. TDE on database files
  5. Audit failed logins + privilege changes
  6. Quarterly access review with sp_helprotect / catalog views

Troubleshooting

Issue Check
Login failed Password policy, account locked, wrong database default
Cannot connect after AG failover Orphan users — ALTER USER WITH LOGIN
TDE stuck encrypting sys.dm_encryption_progress
Audit not writing Disk space, audit spec STATE, permissions 
  -- Fix orphan user
ALTER USER app_api WITH LOGIN = app_api;

Layer authentication, authorization, encryption, and auditing — document who has sysadmin and rotate credentials on schedule.