Back in SQL Server 2008 Microsoft introduced auditing, specifically the Database Audit Specification. It’s pretty good – despite the title I do actually think its nice feature, it pretty much works and doesn’t have much of a performance impact, my problem is not much love has gone into it since it was released in SQL2008. It claims to meet various regulations such as the EU data Protection Directive, HIPAA, PCI DSS and to be fair, I’m sure it does. Assuming to implement it correctly.
For example, I have it enabled on ACS.DimPersonSensitive – as you can guess, it holds sensitive information about a individual. If I want to break it, all I need to do is use parameters, ie
declare @per_id int = 123456<br /> select * from [ACS].[DimPersonSensitive] where per_id = @per_id
Now if I look at the logs, I literary see @per_id, so I have no idea what id that was pass. There is no column in the audit logs that tell me what that parameter was.
The other issue I was hit with today was SSDT, it really doesn’t support it. Here is the error I got today
It’s basically blocking me from changing the table because it has auditing enabled on it. No nice way around it. It’s another Pre-Deployment script. This isn’t terrible. The terrible bit is it doesn’t reapply the auditing once you’ve removed it and updated the table, despite the auditing being defined in SSDT project.