Friday 13 September 2019

How to enable SQL SERVER audit on a particular Table (object)

In order to enable SQL Server Audit on the table, one has to create a Server Audit and Database Audit. Let's create a server audit in MYDB  database and database audit on MYTABLE which is residing in MYDB. Please make sure to replace the appropriate database name and table name.
USE MASTER 
GO 
IF EXISTS (SELECT * FROM SYS.DATABASES WHERE name = 'MYDB') 
ALTER DATABASE MyDB  SET  SINGLE_USER WITH ROLLBACK IMMEDIATE 
DROP DATABASE IF EXISTS MyDB 
GO 
CREATE DATABASE MyDB 
GO 
USE MyDB 
GO 
CREATE TABLE [dbo].[MyTable]([ID] [int] IDENTITY(1,1) NOT NULL,[String] [varchar](150) NULL) 
GO
USE master
GO
--DROP SERVER AUDIT IF EXISTS, BEFORE DROPPING PLEASE MAKE SURE SERVER AUDIT TO STATE = OFF 
IF EXISTS (SELECT * FROM SYS.SERVER_AUDITS WHERE name = 'ServerAudit_MyDB_MyTable')
BEGIN
ALTER SERVER AUDIT [ServerAudit_MyDB_MyTable]    WITH (STATE = OFF) ;
DROP SERVER AUDIT [ServerAudit_MyDB_MyTable]
END
GO
--CREATE SERVER AUDIT
CREATE SERVER AUDIT [ServerAudit_MyDB_MyTable]
TO FILE
(    FILEPATH = N'C:\2019\SEP 2019\AUDIT\' 
--REPLACE CORRECT AUDIT FILES PATH
    ,MAXSIZE = 100 MB
    ,MAX_ROLLOVER_FILES = 90
    ,RESERVE_DISK_SPACE = ON
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE =CONTINUE
)
GO
--TURN STATE = ON 
ALTER SERVER AUDIT [ServerAudit_MyDB_MyTable]    WITH (STATE = ON) ;
GO
--DROP DATABASE AUDIT IF EXISTS, BEFORE DROPPING PLEASE MAKE SURE DATABASE AUDIT TO STATE = OFF 
USE MyDB 
GO
IF EXISTS (SELECT * FROM SYS.DATABASE_AUDIT_SPECIFICATIONS WHERE name = 'DB_Audit_Spec_MyDB_MyTable')
BEGIN
ALTER DATABASE AUDIT SPECIFICATION [DB_Audit_Spec_MyDB_MyTable] WITH (STATE = OFF) ;
DROP DATABASE AUDIT SPECIFICATION [DB_Audit_Spec_MyDB_MyTable]
END
GO
CREATE DATABASE AUDIT SPECIFICATION [DB_Audit_Spec_MyDB_MyTable]
FOR SERVER AUDIT [ServerAudit_MyDB_MyTable]
ADD (SELECT, INSERT,DELETE, UPDATE
     ON [dbo].[MyTable]  BY dbo )
WITH (STATE = ON) 
--TURN STATE = ON 
GO 

--ADD RECORDS 
INSERT MyTable(String) 
SELECT NEWID()GO250


GO
--CHECK RECORDS

SELECT * FROM MyTable

GO

--CHECK IN AUDIT TABLE
SELECT * FROM fn_get_audit_file('C:\2019\SEP 2019\AUDIT\*',default,default)
GO
--Thank you, 
--Narasimha












No comments:

Post a Comment