<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Auditing in SQL server

Published on
13,528 Points
5,728 Views
3 Endorsements
Last Modified:
Approved
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days.

Lets' understand the Audit to know the usage of data and operation on data.

Please follow the steps below and make sure Audit is not available in Standard and Express edition of SQL Server.

Note: Server Audit Configuration will find at Server=> Security=>Audit
and
Database Audit Configuration will find at Server=> Database=> Security=> Database Audit Specification

1. Set the Proper Audit name and Select the Audit location type, (File, Security and Application Log etc.)
 Server Setting

Based on the Audit type may you have question why audit is not at SQL Table level? A simple answer is when audit is enabled and change is recorded then again due to the insertion of that log cyclic insertion occur. that's why audit is not available to save in SQL table.

2. Audit Type is selected as File then select the folder path.
Audit Location
3. Same thing you can create for Server Audit Specification.
 Server Audit Specification
In this step you have predefined options provided by SQL and you can use any of that for your purpose.

i.e If you want to change the permission to data etc. Please make sure you can find the Server and Database level actions.

4. Create Audit Action on particular object (Database objects like Table, View etc.)
 Database Specification
5. Select Option from Action drop down and before that Select the Audit source as shown in below image.
Object Config
That's it for Configure the Server/Database Audit.

Let's see the result of Audit how can we see the result of audit. Who has viewed the data, updated data or deleted data etc. etc.

For, this SQL provide the Management function to read the Audit files and give result step by step.

SELECT
* FROM fn_get_audit_file(
'H:\PefLogs\Audit-FailedLogin_6A8681AA-5555-4EA9-AA18-D18A552E1ACF_0_129216903300500000.sqlaudit',
default
, default)

Open in new window


After executing this log you can get the result as below:
Result
3
Comment
2 Comments
LVL 17

Expert Comment

by:Paulo Pimenta
Truth! This is indeed a very useful article, since many IT teams tend to, themselves, forget a bit about auditing issues. By my experience, this resource only becomes useful when it isn't used, so there's no harm in setting it up.
Thanks,
PP
0

Expert Comment

by:tera baap
not at all usefull. Biggest chutiappa I have ever seen on this forum.
0

Featured Post

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Join & Write a Comment

Viewers will learn how the fundamental information of how to create a table.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month