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
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
--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
BEGIN
ALTER SERVER AUDIT [ServerAudit_MyDB_MyTable] WITH (STATE = OFF) ;
DROP SERVER AUDIT [ServerAudit_MyDB_MyTable]
END
GO
--CREATE SERVER AUDIT
--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
)
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
--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) ;
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
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
INSERT MyTable(String)
SELECT NEWID()GO250
GO
--CHECK RECORDS
--CHECK RECORDS
SELECT * FROM MyTable
GO
--CHECK IN AUDIT TABLE
SELECT * FROM fn_get_audit_file('C:\2019\SEP 2019\AUDIT\*',default,default)
SELECT * FROM fn_get_audit_file('C:\2019\SEP 2019\AUDIT\*',default,default)
GO
--Thank you,
--Narasimha
--Thank you,
--Narasimha
No comments:
Post a Comment