Share via

SQL Server 2008 R2 - Power User Audit Sample (DDL Trigger)

  • Article

-- Create a database
CREATE DATABASE DDLTrigger
GO

USE DDLTrigger
GO

-- Create a table to hold results
CREATE TABLE info_ddl (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100))
GO

-- Now create a Trigger
CREATE TRIGGER ddl_test
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT info_ddl
 (PostTime, DB_User, Event)   
 VALUES   
 (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER),
 EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'))
GO

-- Create a table in Database
CREATE TABLE TestDDlTrigger (a int,b int,c int)
GO

-- Create procedure in Database
CREATE PROCEDURE spTest
AS
SELECT * FROM sys.all_objects
GO

-- Check our table which holds the data
SELECT * FROM info_ddl
GO

-- Now we drop the table and procedure from Database
DROP TABLE TestDDlTrigger;
GO

DROP PROCEDURE spTest
GO

-- Again check the audit data
SELECT * FROM info_ddl
GO

-- Adding additional column to table to store T-SQL
ALTER TABLE info_ddl ADD TSQL VARCHAR(2000)
GO

-- Recreate the DDL Trigger
DROP TRIGGER ddl_test
ON DATABASE
GO

CREATE TRIGGER ddl_test
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
INSERT info_ddl   
 (PostTime, DB_User, Event, TSQL)
 VALUES
 (GETDATE(), CONVERT(nvarchar(100), CURRENT_USER),
 EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
 EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
GO

-- Create a table in Database
CREATE TABLE TestDDlTrigger (a int,b int,c int)
GO

-- Create procedure in Database
CREATE PROCEDURE spTest
AS
SELECT * FROM sys.all_objects
GO

-- Check our table which holds the data
SELECT * FROM info_ddl
GO

 

REFERENCES: