Creating a Database Audit Specification can create new users & schemas in the background

2

June 28, 2022 by Kenneth Fisher

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 = 'domain\KFisher';
SELECT * FROM sys.database_Principals WHERE name = 'domain\KFisher';
SELECT * FROM sys.schemas WHERE name = 'domain\KFisher';

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 [domain\KFisher]);
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 [domain\KFisher];
DROP USER [domain\KFisher];
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];

2 thoughts on “Creating a Database Audit Specification can create new users & schemas in the background

  1. […] Kenneth Fisher asks, who audits the auditors?: […]

  2. […] I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,738 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: