The auditing session allows you to record events that occurring at server level or database level in file or in the windows event log. You can easily setup audit in SQL Server 2012 with Server Audit, define an audit and bind a specification that contains events to collect it. Database level auditing is only available in the Enterprise Edition. Follow the below steps to setup SQL Server Audit
1. Open SQL Server management studio , go to the security node under instance node then right-click on Audits and click new Audit
2. Enter the name for your audit, and add a file-path for audit to be written
3. Click ok to create the server audit and then right-click on the node below named Server Audit Specifications. Click on New Audit Specification
4. In New Audit Specification window , enter a name and bind the specification to the audit that you have created above and then add relevant action types as shown below
5. Right click on the audit specification that you have created in the above step and then click Enable Server Audit Specification. Similarly right-click on audit and click Enable Server Audit.
6. You can also specify Audit at database level, navigate to the database in security node and then right-click on Database Audit Specifications. Click on new Database Audit Specification.
7. To view the audit-logs that created in above steps right-click on the audit and click on View Audit log.
8. You can write the below SQL-Query to read the all the audit-files specified in the directory
1: SELECT * FROM sys.fn_get_audit_file
2: ('C:\sqldata\Audit\*', default, default);
| Share this post : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
