SQL Server Database Auditing not working for Sysadmin users

Question

I'm trying to Audit Sysadmin users at Database level; however, none of the SELECTS, INSERTS, UPDATES and DELETES are being audited.

I created the Server Audit, followed by the Server Audit specification ADD (DATABASE_OBJECT_ACCESS_GROUP) and then the Database Audit specification to audit the database as a whole: ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES ON DATABASE::TestAuditDB BY newsa2);

I tested it by INSERTing and SELECTing with that user 'newsa2'; however, no audit entries were found.

I need very specific entries for each sysadmin user to be entered into the Audit log

Here is my code:

USE [master]
GO

DROP SERVER AUDIT [Audit_sql2016]
TO FILE 
(    FILEPATH = N'C:\Audit\SQL2016'
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
);
GO

CREATE SERVER AUDIT SPECIFICATION [Audit_sql2016Specification]
FOR SERVER AUDIT [Audit_sql2016]
   ADD (DATABASE_OBJECT_ACCESS_GROUP) 
WITH (STATE = OFF);
GO

ALTER SERVER AUDIT SPECIFICATION [Audit_sql2016Specification]
FOR SERVER AUDIT [Audit_sql2016]
WITH (STATE = ON);

ALTER SERVER AUDIT Audit_sql2016 WITH (STATE = OFF)
GO

USE TestAuditDB
GO

DROP DATABASE AUDIT SPECIFICATION [Audit_sql2016SpecificationDatabase]
FOR SERVER AUDIT [Audit_sql2016]
      ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES ON DATABASE::TestAuditDB BY newsa2);

ALTER DATABASE AUDIT SPECIFICATION [Audit_sql2016SpecificationDatabase]
--FOR SERVER AUDIT [Audit_sql2016]
WITH (STATE = ON);

pifor · Answer 1 · 2020-06-29T12:48:32.177

0

I have adapted and fixed your script (there is a missing step to enable SERVER AUDIT - I noticed with SQL Server Management Studio where there was a red cross for related icon):

USE [master]
GO

ALTER SERVER AUDIT [audit_server] WITH (STATE=OFF)
GO

DROP SERVER AUDIT [audit_server]
GO

ALTER SERVER AUDIT SPECIFICATION [audit_spec] WITH (STATE = OFF)
GO

DROP SERVER AUDIT SPECIFICATION [audit_spec]
GO

CREATE SERVER AUDIT [audit_server]
TO FILE 
(    FILEPATH = 'C:\Audit'
)
WHERE database_name='test';
GO

ALTER SERVER AUDIT [audit_server] WITH (STATE = ON);
GO

CREATE SERVER AUDIT SPECIFICATION [audit_spec]
FOR SERVER AUDIT [audit_server]
WITH (STATE = OFF);
GO

ALTER SERVER AUDIT SPECIFICATION [audit_spec]
FOR SERVER AUDIT [audit_server]
ADD (DATABASE_OBJECT_ACCESS_GROUP)
WITH (STATE = ON);

USE Test
GO

ALTER DATABASE AUDIT SPECIFICATION [audit_db]
WITH (STATE = OFF);
GO

DROP DATABASE AUDIT SPECIFICATION [audit_db]
GO

CREATE DATABASE AUDIT SPECIFICATION [audit_db]
FOR SERVER AUDIT [audit_server]
      ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES ON DATABASE::test by public);
GO

ALTER DATABASE AUDIT SPECIFICATION [audit_db]
WITH (STATE = ON);
GO

With this setup I can have in audit following DML statements run by user dbo in database test (corresponding login has sysadmin role):

use  test
go
delete from t;
go
insert into t values(1);
go

Tested with SQL Server 2019.

You can audit only a specific schema with:

CREATE DATABASE AUDIT SPECIFICATION [audit_db]
FOR SERVER AUDIT [audit_server]
      ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES ON SCHEMA::myschema by public);
GO

edited Jun 29 '20 at 12:48

answered Jun 29 '20 at 10:26

pifor

7,419
2
8
16

Could I audit only for a particular sa user like this: ``` CREATE DATABASE AUDIT SPECIFICATION [audit_db] FOR SERVER AUDIT [audit_server] ADD (SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCES ON DATABASE::Test by newsa2); GO ``` – ugp Jun 29 '20 at 12:03
I am not sure this is possible as `sa` is a login that is mapped to `dbo` in databases. I understand that you can only use database principals. So it is ok if `newsa2` is a database user but it does not work if `newsa2` is a login. – pifor Jun 29 '20 at 12:16
There is a lot of noise in the logs - almost 94 percent. Could I audit on the SCHEMA instead? – ugp Jun 29 '20 at 12:44
Yes you can only audit a specific schema: I have updated my answer with an example. – pifor Jun 29 '20 at 12:49
Should we add the SCHEMA_OBJECT_ACCESS_GROUP to the Audit Server specification? I want to audit only the dbo schema, as the sys schema audits are generating too much noise. – ugp Jun 29 '20 at 12:53
I want to know - if we specify the audit Destination to be the Windows Application log, can we read the contents using the sys.fn_get_audit_file, as we do for File audits. – ugp Jun 29 '20 at 14:44

score 0 · Answer 2 · answered Jun 29 '20 at 16:13

0

Should we add the SCHEMA_OBJECT_ACCESS_GROUP to the Audit Server specification? I want to audit only the dbo schema, as the sys schema audits are generating too much noise. Ans: Not required. The DATABASE_OBJECT_ACCESS_GROUP takes care of this as well.

answered Jun 29 '20 at 16:13

ugp

119
11

I have a question, there is too much Auditing in the sys schema when the SCHEMA_OBJECT_ACCESS_GROUP is selected. Is there a way to stop this? It seems every schema is audited once this SCHEMA_OBJECT_ACCESS_GROUP is added. – ugp Jul 02 '20 at 04:25

SQL Server Database Auditing not working for Sysadmin users

2 Answers2