SQL Server › SQL Server Security & Permissions

How would you design a comprehensive security auditing strategy to track who accessed or modified sensitive data in a SQL Server database?

Advanced

Answer

You would enable SQL Server Audit to capture specific security relevant events, such as successful and failed login attempts and access to particularly sensitive tables, direct that audit data to a secure location like a file or the Windows Event Log, and establish a regular review process to examine the captured audit data for suspicious patterns or compliance verification purposes.

CREATE SERVER AUDIT SecurityAudit
TO FILE (FILEPATH = 'C:\Audits\');

CREATE DATABASE AUDIT SPECIFICATION SensitiveDataAudit
FOR SERVER AUDIT SecurityAudit
ADD (SELECT ON dbo.Customers BY public);

ALTER SERVER AUDIT SecurityAudit WITH (STATE = ON);

Real-world example A financial services company sets up comprehensive SQL Server Audit tracking on their customer data tables, satisfying regulatory compliance requirements by maintaining a detailed, tamper evident record of exactly who accessed sensitive customer information and when.

Common follow-ups: What compliance regulations commonly require this kind of detailed database auditing?;How do you ensure audit logs themselves cannot be tampered with by someone trying to hide their activity?

Dynamic Data Masking;Transparent Data Encryption