Thursday, April 7, 2011

Audits in sql server 2008

Just noticed something new which started in sql server 2008.
Audits.

You can create and enable audits so all the related information like login success/failures, db backups/restores etc. will be logged in your audit file.
Its relatively simple to do.
I think this feature exists only in enterprise edition after sql server 2008

In order to create one here is the simple procedure:
1) create audit  by going to SSMS-> security tab->Audits tab
You can either choose the audit to be logged in windows application event log or security event log or your own file.

CREATE SERVER AUDIT [Audit-20110407-190438]
TO FILE
(    FILEPATH = N'W:\Audit\'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '824f0cb7-328a-4875-89aa-dcf42ba7bceb'
)
GO

2) Once done, you need to create server audit specifications where each one should be associated with an Audit.
for each server audit specification, you can use events to be audited like "backup/restore", "login success, login failure, db schema change, etc.

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20110407-190608]
FOR SERVER AUDIT [Audit-20110407-190438]
ADD (BACKUP_RESTORE_GROUP),
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO

3) You can also enable DB audit specifications like "delete, insert, update, dbcc" etc.
so its like whenever user Test inserts some data in a particular table, it will be logged in the audit file


CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20110407-192130]
FOR SERVER AUDIT [Audit-20110407-190438]
ADD (UPDATE ON OBJECT::[RetailSales].[CustomerRetailerXRef] BY [db_owner]),
ADD (DELETE ON OBJECT::[dbo].[Profiles] BY [db_owner])
WITH (STATE = ON)
GO
I've created all these using SSMS GUI but you can do using scripts as well like mentioned above.
this ofcourse fills up your audit log file, so make sure you drive has enough space.

You can query your audit file using SSMS or from the below queries:

SELECT * FROM sys.fn_get_audit_file
 ('w:\Audit\Audit-20110407-190438_824F0CB7-328A-4875-89AA-DCF42BA7BCEB_0_129467019443560000.sqlaudit',default,default)

You can get your audit file information using the below query
select * from sys.server_audits
select * from sys.server_audit_specifications

 select * from sys.database_audit_specifications

One more function to get the Audit info from DMVs

select * from sys.dm_audit_actions

No comments:

Post a Comment