I love database audits. They are simple, easy to use, effective, not overly resource intensive, and can be turned on and off at need once created. That said, they do have a few gotchas. If you want every user put public as the principal. And if you don’t, and you put in an AD user, be aware that if that user will be created (along with a matching schema) when you create the Database Audit Specification.
USE [DBName];
GO
SELECT * FROM sys.server_principals WHERE name = 'domainKFisher';
SELECT * FROM sys.database_Principals WHERE name = 'domainKFisher';
SELECT * FROM sys.schemas WHERE name = 'domainKFisher';
A quick check ahead of time to make this a valid test. Note that the server principal, database principal, and schema don’t exist. Next we create the audit and the database specification. Just a simple one.
USE [master]
GO
CREATE SERVER AUDIT [CreateAUser]
TO FILE
(FILEPATH = N'c:temp' /*Don't use c:temp in real life. */,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO
USE [DBName];
GO
CREATE DATABASE AUDIT SPECIFICATION [CreateAUserSpec]
FOR SERVER AUDIT [CreateAUser]
ADD (SELECT ON SCHEMA::[dbo] BY [domainKFisher]);
GO
Now we check again:
You’ll notice that suddenly we have an orphaned user and a new schema. A server principal is not created, but the database principal and schema are. I’m not going to demo it here but to the best of my knowledge the schema can be removed, but the user needs to be there for the audit to work properly.
If you’re interested we discovered this because our systems DBAs put in a series of audits to check what a group of people were doing. All of a sudden we had individual users created all over the place on databases where we’d only used AD groups for permissions up to that point. We had to clean up when they were done but that wasn’t a big deal. And speaking of cleanup.
USE [DBName];
DROP SCHEMA [domainKFisher];
DROP USER [domainKFisher];
ALTER DATABASE AUDIT SPECIFICATION [CreateAUserSpec] WITH (STATE = OFF);
DROP DATABASE AUDIT SPECIFICATION [CreateAUserSpec];
USE [master];
ALTER SERVER AUDIT [CreateAUser] WITH (STATE = OFF);
DROP SERVER AUDIT [CreateAUser];