SQL SERVER – Who ALTER’ed My Database? Catch Them Via DDL Trigger

Here is an interesting comment from one of my previous blogs on how to change the compatibility level of SQL server post posted earlier. Some of these comments are interesting and can provide some interesting extension to blog ideas like this. So the comment was:

Under what circumstances would SQL Server automatically change the compatibility level ? I am currently working on SQL Server 2008 and there is a user database which has a compatibility level of 80. Apparently this database was moved to this server prior to my time, and the compatibility level was never changed/upgraded. When I execute the following script, it is set successfully to 100, however, the next day, it goes back to 80. Any ideas ?

USE [master] GO
ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 100
GO


There is only a SQL Maintenance plan to perform integrity check, full database backups / transaction log backups, index rebuild/update stats on this instance.

Here is my reply:

SQL Server doesn’t do that automatically. There must be some automated activity doing it.

When the reader asked how to track such changes done in SQL Server. I had replied separately over mail and now sharing with you. I tried to write a script, which would create a DDL trigger on ALTER database command. Below script would do below things.

1. Find who fired ALTER DATABASE Command.
2. From which Application it was fired.
3. What was the exact command fired.

Here is the code of the trigger.

IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'DDL_Database_Trigger')
DROP TRIGGER DDL_Database_Trigger
ON ALL SERVER;
GO
CREATE TRIGGER DDL_Database_Trigger
ON ALL SERVER
FOR ALTER_DATABASE
AS
DECLARE @cmd VARCHAR(200)
DECLARE @who VARCHAR(200)
DECLARE @fromwhere VARCHAR(200)
SELECT @cmd = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nVARCHAR(max)')
SELECT @who = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nVARCHAR(max)')
SELECT @fromwhere = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]','nVARCHAR(max)')
RAISERROR (N'!!!Database ALTERED!!!
Command - %s
By - %s
From - %s.',
10,
1,
@cmd, @who, @fromwhere) WITH LOG
GO

For testing purpose, I fired below two commands:

USE [master] USE [master] GO
ALTER DATABASE [SQLAuthority] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [SQLAuthority] ADD FILE
( NAME = N'MM',
FILENAME = N'E:\InvalidPath\MM.ndf', SIZE = 5120KB, FILEGROWTH = 1024KB)
TO FILEGROUP [PRIMARY]

And here is what I saw in ERROROG

If you have not pre-configured DDL trigger to catch that information in ERRORLOG, don’t worry. You still have some information logged in default trace. Go through below blog where I explored Standard Reports which are available out of the box with SQL Server.

SQL SERVER – SSMS: Schema Change History Report

Have you ever used such tricks on your server? Do let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)