History of all Committed DDL Statement Executions

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’)

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

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 .

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.