Maintain your DDL commands log & Restrict DDL commands in SQL Server 2005
1. Create Audit Schema( which ever schema name you like it)
CREATE SCHEMA Audit
GO
2. Create table to hold DDL command log data
CREATE TABLE [Audit].[DDLLog](
[id] [int] IDENTITY(1,1) NOT NULL,
[logData] [xml] NOT NULL,
CONSTRAINT [PK_DDLLog] PRIMARY KEY CLUSTERED
([id] ASC)) ON [PRIMARY]
GO
3. Create trigger to store DDL command in table when any DDL command executed in DB
CREATE TRIGGER [Trig_DDLLog]
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT INTO [Audit].[DDLLog](logData)
SELECT EVENTDATA()
GO
4. Enable Trigger
ENABLE TRIGGER [Trig_DDLLog] ON DATABASE
Thats all. Now on if you use any DDL command on DB, it maintains log in Audit.DDLLog table.
____________________________________________________________
Restrict DDL commands :
Below Trigger restrict user from Droping tables and Altering tables. If you wanted to do that you must disable trigger first before you proceed to Alter or Drop table.
CREATE TRIGGER Trig_RestrictDropAlterTable
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘You must disable Trigger “Trig_RestrictDropAlterTable” to drop or alter tables!’
ROLLBACK
To Disable Trigger
DISABLE TRIGGER [Trig_RestrictDropAlterTable ] ON DATABASE
To provide next level of security from Droping and Altering tables, you can create schema, and give permissions to only one main user on newly created schema. Create this trigger under that schema. So others don’t have permission to disable trigger. So others can’t be able to modify DB structures. Only main users will disable trigger and do nessasary changes in structures.
