Sql-server – How to Audit 2 or more SQL Logins (server principals) using SQL Server Audit

auditsql server

I need to audit the few SQL Server Logins, I have used filters while creating Server Audit by using 

CREATE SERVER AUDIT [audit123]
TO FILE 
(   FILEPATH = N'D:\'
    ,MAXSIZE = 2048 MB
    ,MAX_ROLLOVER_FILES = 6
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 5000
    ,ON_FAILURE = CONTINUE
)
WHERE ([server_principal_name]='AAA')

But when I am including more SQL logins using where ([server_principal_name] in ('AAA','BBB','CCC')) it's giving error. I have tried using below but failed to create audit for 2 or more logins.

ALTER SERVER AUDIT audit123 WHERE server_principal_name ='BBB';
ALTER SERVER AUDIT audit123 WHERE server_principal_name ='CCC';

Best Answer

I believe you are using a part of SQL server audit specification in very first step:

  1. Firstly you just need to create a server audit object using SQL Server Audit. Now in this audit section you are using where clause, which also requires a creation of sql server audit specification:

  2. Check the permissions for altering an audit:

    a)To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

    b)Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.

Refer to below eg : from MSDN whihc might help you in understanding the audit using principals in where clause:

  CREATE DATABASE TestDB;
  GO
  USE TestDB;
  GO
  CREATE SCHEMA DataSchema;
  GO
  CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
  GO
  CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
   GO
    -- Create the server audit in the master database
 USE master;
 GO
  CREATE SERVER AUDIT AuditDataAccess
  TO FILE ( FILEPATH ='C:\SQLAudit\' )
 WHERE object_name = 'SensitiveData' ;
 GO
 ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);
  GO
 -- Create the database audit specification in the TestDB database
  USE TestDB;
  GO
CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]
FOR SERVER AUDIT [AuditDataAccess] 
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
WITH (STATE = ON);
GO
-- Trigger the audit event by selecting from tables
 SELECT ID, DataField FROM DataSchema.GeneralData;
 SELECT ID, DataField FROM DataSchema.SensitiveData;
 GO
 --- Check the audit for the filtered content
  SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);
   GO
Related Question