AUDITING in SQL Server

SQL Server 2008 allows you audit the securable include server, database and schema. It allows us to audit the confidential information. Suppose if we want to know who had accessed the employee salary Information. Create table for payroll information.

CREATE TABLE [dbo].[emp_payrol](

      [empid] [int] NOT NULL,

      [payrolldate] Date NOT NULL,

      [salary] [decimal](18,2) NULL

) ON [PRIMARY]

GO

Using SQL Server audit we can identify the users who has accessed the emp_payrol.In order audit the emp_payroll table first we need to create audit.

The below script created Audit emp_payrol access.

CREATE SERVER AUDIT [Audit emp_payrol access]

TO FILE

(     FILEPATH = N'D:\SQL audit\'  --make sure that  you use valid path

      ,MAXSIZE = 0 MB   -- unlimited

      ,MAX_ROLLOVER_FILES = 2147483647

      ,RESERVE_DISK_SPACE = OFF  -- if size is unlimited reserve disk space ----- will set zero

)

WITH

(     QUEUE_DELAY = 1000

      ,ON_FAILURE = CONTINUE  --on failure continue we have shut_down option -- is available

      ,AUDIT_GUID = '8123ae2b-4985-445a-ac62-9ab459643176' -- select newid()

)

GO

Once we have created the server audit. We need to specify the server audit specification.

CREATE SERVER AUDIT SPECIFICATION [PayrollServerAudit]

FOR SERVER AUDIT [Audit emp_payrol access]

ADD (DATABASE_OBJECT_ACCESS_GROUP)

WITH (STATE = ON)

Server audit specification will be used for specifying the audit group to audit.

State specifies whether server audit specification is enabled or disabled.

ON – enabled

OFF – disabled

DATABASE_OBJECT_ACCESS_GROUP : AUDIT uses the database audit access group.includes auditing select ,insert ,update.
Once we have created the server audit specification. We can create the database audit specification using the below query.

CREATE DATABASE AUDIT SPECIFICATION [PayrollDatabaseAudit]

FOR SERVER AUDIT [Audit emp_payrol access]

ADD (SELECT ON OBJECT::[dbo].emp_payrol BY [dbo])

WITH (STATE = ON)

GO

--ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification]

--FOR SERVER AUDIT [Audit Emp table access]

--ADD (UPDATE ON OBJECT::[dbo].[emp] BY [dbo]), --you can use the multiple ---- commands like insert ,select,update

--ADD (select ON OBJECT::[dbo].[emp] BY [dbo])

--WITH (STATE = ON)

Once you have executed the above scripts. You enable sql server audit.

Using the table valued function fn_get_audit_file we can retrieve the user details who has accessed the table.Execute the command to access emp_payroll table

SELECT TOP 1000 [empid]

      ,[payrolldate]

      ,[salary]

  FROM [SnapshotDB].[dbo].[emp_payrol]

To verify the table “emp_payrol” access details use the following command.

The file will be be saved in the D:SQL_audit folder.

To fetch the details from the file use the below query.

SELECT action_id,server_principal_id,

database_name,object_name,statement

 FROM

 fn_get_audit_file('D:\SQL audit\Audit%5emp_payrol%5access_8123AE2B-4985-445A-AC62-9AB459643176_0_130310813658880000*.sqlaudit',default,default);

action_id column showing in the table 

actiond_id SL-- select

object_name --emp_payroll

server principal id -- 261

statement - query executed by user

by using the system view sys.server_principals we can get the principal name.