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
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
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
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
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
then now i will insert Data :
INSERT INTO TABLEAUDIT ([EMP_NAME],[EMP_SALARY],[EMP_BOUNS],[EMP_ADDRESS])
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.