Auditing in sql server 2008 !

Q) What is the syntax for creating a server audit ?
A)
USE [master]

GO

CREATE SERVER AUDIT [audit1]
TO FILE
( FILEPATH = N'C:\audits'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = ON
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

GO

Q) Can a server audit be written only to a file ?
A)
No.We can store the audits not only in a file but also in either security log or application log.

Q) Give syntax/example of creating a server audit into a security log.
A)
USE [master]

GO

CREATE SERVER AUDIT [audit2_sec]
TO SECURITY_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)

GO

Q) Give syntax/example of creating a server audit into a application log.
A)
USE [master]

GO

CREATE SERVER AUDIT [audit3_app]
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)

GO

Q) Are the server audits once created enabled by default ?
A) No.We need to enable them after creating it with the alter statement.

Q) How to enable a server audit ?
A) Use the below t-sql statement :
GO
ALTER SERVER AUDIT audit1
WITH (STATE = ON);

Q) How to Change a server audit name ?
A)
USE master
GO
ALTER SERVER AUDIT audit1
WITH (STATE = OFF);
GO
ALTER SERVER AUDIT audit1
MODIFY NAME = audit1_Old;
GO
ALTER SERVER AUDIT audit1_Old
WITH (STATE = ON);
GO

Q) What is the pre-requisite for performing any alter statement on server audits ?
A)
Turn the server audit state to off,perform the alter statement and then turn it back on.

Q) How to drop a server audit ?
A)
ALTER SERVER AUDIT audit1_Old
WITH STATE = OFF;
GO
DROP SERVER AUDIT audit1_Old;
GO


Q) How to view all the active/enabled server audits in an instance ?
A)
select * from sys.dm_server_audit_status

Q) How to view all the existing server audits in an instance ?
A)
select * from sys.server_audits

Q) How to view all the existing server audits that are configured to store audit records into a file system ?
A)
select * from sys.server_file_audits

Q) How to create a server audit specification ?
A)
USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [su_spec_01]
FOR SERVER AUDIT [audit1]
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP)

GO

Q) Explain various sql server audit action groups.
A)
successful_login_group
failed_login_group
login_change_password_group
logout_group

server_object_change_group
server_object_ownership_change_group
server_principal_change_group
server_principal_impersonation_group
server_role_member_change_group
server_operation_group
server_state_change_group
server_permission_change_group
server_object_permission_change_group

database_role_member_change_group
database_operation_group
database_change_group
database_object_change_group
database_principal_change_group
database_principal_impersonation_group
database_ownership_change_group
database_object_ownership_change_group
database_permission_change_group
database_object_permission_change_group
database_object_access_group
application_role_change_password_group

schema_object_change_group
schema_object_ownership_change_group
schema_object_permission_change_group
schema_object_access_group

backup_restore_group
dbcc_group
trace_change_group
broker_login_group
database_mirroring_login_group

audit_change_group

Q) Is server audit specification enabled by default ?
A) No.

Q) How to enable server audit specification ?
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=ON)

Q) I have created a server audit specification by name [su_spec_01] for server audit [audit1] that contains 2 groups namely :server_object_change_group and database_change_group.Now I wanted to remove the sever_object_change_group from this specification.Give syntax.
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=OFF)

ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
DROP (SERVER_OBJECT_CHANGE_GROUP);

ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=ON)

Q) How to drop a server audit specification ?
A)
ALTER SERVER AUDIT SPECIFICATION [su_spec_01]
WITH (STATE=OFF);
--first we need to disable it and then drop them.

DROP SERVER AUDIT SPECIFICATION [su_spec_01];

Q) How to view all the existing server audit specifications ?
A)
select * from sys.server_audit_specifications

Q) How to view all the active/enabled server audit specifications ?
A)
select * from sys.server_audit_specifications.
here,is_state_enabled column will tell us if its enabled or disabled.
enabled-1
disabled-0

Q) How to know all the groups configured for server audit specifications ?
A)
select * from sys.server_audit_specification_details

Q) How to know the list of every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit ?
A)
select * from sys.dm_audit_actions.
This returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit.

Q) How to read information from the audited log file ?
A)
select * from sys.fn_get_audit_file('C:\audits\*.sqlaudit',default,default);

Q) There is a column called "class_type" in the dynamic fn "sys.fn_get_audit_file".Now how to find this class_type description of this class_type column ?
A)
select * from sys.dm_audit_class_type_map
example :
class_type class_type_desc securable_class_desc
DB DATABASE DATABASE

This actually returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.


Q) How to create a database audit specification ? Give an example.
A)
CREATE DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
FOR SERVER AUDIT [audit2_sec]
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (DELETE ON OBJECT::[dbo].[tab] BY [dba1]),
ADD (SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);

GO

Remember before creating a database audit specification,we should have already created a server audit(here its audit2_sec).

Q) Is database audit specification enabled by default ?
A) No.

Q) How to enable a database audit speification ?
A)
ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH (STATE=ON)

Q) I have by mistake added the following to db audit specification [db_audit_spec_01].Now I want to remove this.Give syntax.
(SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);
A)

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=OFF);

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
DROP (SELECT,INSERT ON OBJECT::[dbo].[tab] BY [dba1]);

ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=ON);

Q) How to drop a database audit specification ?
A)
ALTER DATABASE AUDIT SPECIFICATION [db_audit_spec_01]
WITH(STATE=OFF);

DROP DATABASE AUDIT SPECIFICATION [db_audit_spec_01];

Q) How to view all the existing database audit specifications in an instance ?
A)
select * from sys.database_audit_specifications

Q) How to view all the active/enabled database audit specifications in an instance ?
A)
select * from sys.database_audit_specifications
here the column "is_state_enabled" tells us if this is enabled or disabled.
enabled-1
disabled-0

Q) How to know all the groups configured for a database audit specification ?
A)
select * from sys.database_audit_specification_details