SQL Server :Creates a database audit specification object using the SQL Server audit feature

You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.

You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.

Which four Transact-SQL statements should you use?

Answer:

With the help from following T-SQL, we'll be able to creates a server audit called C_Audit and then a database audit specification called C_AuditSpec that audits Update statements by the dbo user, for the [Sales].[ProductPrices] table in the ContosoDb database.

1.

use master

2.

create server audit C_Audit to application_log
alter server audit C_Audit with (State = on)

3.

use ContosoDb

4.

create database audit specification C_AuditSpec
for server audit C_Audit
Add (update on [Sales].[ProductPrices] by DBO)
alter database audit specification C_AuditSpec
with (State = on)

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)