RSS

History of all Committed DDL Statement Executions

18 May

Now if i need to Make Auditing in my SQL Server production to know any thing happens in my SQL Server  We have 3 Ways :

1- We can Use third party tool like this one for free

http://www.toadworld.com/KNOWLEDGE/ToadKnowledge/TipsandTricks/tabid/74/TID/303/cid/38/Default.aspx

2- use SQl Server Report to Audit your SQL Server . it Will give you Summary about the Object Changed like Create , Drop , Alter .

3- Use SQL Server Audit Feature . Under Security  in SQL Server SSMS . it can give you any info you need to know it.

I will Explain today the How to check the History of all Committed DDL Statement by SQL Server Reports.

in the first i will create table to Explain my Examples on it

Create Database Audit

go

use Audit

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TABLEAUDIT](
[EMP_ID] [int] IDENTITY(1,1) NOT NULL,
[EMP_NAME] [nvarchar](50) NULL,
[EMP_SALARY] [bigint] NULL,
[EMP_BOUNS] [bigint] NULL,
[TOTAL_SALARY] AS ([EMP_SALARY]+[EMP_BOUNS]),
[EMP_ADDRESS] [nvarchar](100) NULL,
CONSTRAINT [PK_TABLEAUDIT] PRIMARY KEY CLUSTERED
(
[EMP_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

then now i will insert Data :

—INSERT DATA
INSERT INTO TABLEAUDIT ([EMP_NAME],[EMP_SALARY],[EMP_BOUNS],[EMP_ADDRESS])
VALUES
(‘Mostafa’,2500,500,’Egypt-Cairo-Helwan’)
, (‘M.Osman’,3000,500,’Egypt-Alex’)
, (‘M.Omar’,3000,500,’Egypt-Assuit’)

Create Example

SQL Server Reports to Audit your SQL production :

1- Schema Changes History Reports

Open SSMS >>> Database Audit >>>> Right Click on it >>>> Reports >>>> Stander Reports >>>> Schema Changes History Report .

you will see the Changes happens in database Audit but only in the Create and Alter and if you make and Drop to Column , table , View Like this you can reviwe it by the time and How make this action from this report on this database

Schema Changes History Report

also you can check any changes happens in your server on all database_user by this steps :

Open SSMS >>>> Connect to your instance you need to make audit on it >>>> right click on your server name >>>> Reports >>>> Stander Reports >>>> Schema Changes History report .

Schema Changes History Report On Server

so now we can know a history of all Committed DDL Statement Executions Recorded. but not give us all info like the DML Statement Executions.

 
Leave a comment

Posted by on May 18, 2013 in Performance MSSQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s