• Adam Thurgar

Oncology App - Auditing


Dealing with personal and private data is difficult. You just don't need to know about inserts, updates and deletes (see previous blog on CDC), but you also need to know who has viewed (selected) your data. You need to know that only people who need to see your data, actually see your data. You do not want unauthorized read access.

In this case we created a server audit.

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine

This was specified to go to a filepath (to a folder that could be locked down) with an unlimited maxsize.

There will be a performance impact on the IO subsystem.

The server audit then needs to be enabled. Then the audit specification was created.

Example: SELECT,INSERT, UPDATE, DELETE ON SCHEMA::[dbo] BY public

and this needs to be enabled.

To report on the audit file you use sys.fn_get_audit_file. You can also then join this with the CDC schema for a complete overview of data reads and writes.


4 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t