Next on the list to commission a SQL Server, is to create a trigger in the master system database to send an e-mail to the DBA when objects are created in the database.
In general I’d expect this never to happen - but if it did, I’d want to follow up. It’s possible to go even further and prevent creating objects in the master system database completely.
Thomas’s “but it worked for me” disclaimer: before using any code you find on the internet, especially on this blog, take time to understand what the code does and test, test, test. I’m not responsible for damage caused by code from this blog, and don’t offer any support or warranty.
--create a trigger in the master system database to e-mail DBA (using Database Mail)
--on schema changes e.g. object creation, update or deletion in the master database
--depending on security settings, most likely need high-level ("sa") permission to create this trigger
--note if there's an issue in this trigger, may impact object creation in master database
--so suggest not adding too much logic to the trigger
--requires Database Mail to be set up, and a default profile to send
--tested with SQL Server 2016, SQL Server 2019
--adapted from https://www.julian-kuiters.id.au/ddl-trigger-prevent-and-notify-ddl-changes/
USE [master]
GO
--drop trigger if it exists
IF EXISTS (SELECT * FROM sys.triggers WHERE [name] = N'trgEmailDbaOnMasterDatabaseObjectCreation') BEGIN
DROP TRIGGER [trgEmailDbaOnMasterDatabaseObjectCreation] ON DATABASE
END
GO
/* trgEmailDbaOnMasterDatabaseObjectCreation
By Thomas Williams https://github.com/thomasswilliams
DDL trigger to send e-mail on schema changes e.g. objects created, altered or
deleted in the master database. Will potentially send multiple e-mails for a
single object created, depending on the number of different statements executed.
Adapted from https://www.julian-kuiters.id.au/ddl-trigger-prevent-and-notify-ddl-changes/
*/
CREATE TRIGGER [trgEmailDbaOnMasterDatabaseObjectCreation]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
--command text (DDL statement), database name from EVENTDATA()
DECLARE @CommandText NVARCHAR(MAX), @DatabaseName NVARCHAR(MAX)
--e-mail subject, body
DECLARE @EmailSubject NVARCHAR(255), @EmailBody NVARCHAR(MAX)
--get EVENTDATA, available inside DDL trigger
DECLARE @xmlEventData XML = EVENTDATA()
BEGIN TRY
--get command text and database name from EVENTDATA XML
SET @CommandText = @xmlEventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)')
SET @DatabaseName = @xmlEventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'NVARCHAR(MAX)')
--ignore changes to this trigger and statistics updates to "CommandLog"
--(Ola Hallengren backup) - otherwise, send e-mail to DBA
IF (
UPPER(@CommandText) NOT LIKE N'%DROP TRIGGER %TRGEMAILDBAONMASTERDATABASEOBJECTCREATION%' AND
UPPER(@CommandText) NOT LIKE N'%ALTER TRIGGER %TRGEMAILDBAONMASTERDATABASEOBJECTCREATION%' AND
UPPER(@CommandText) NOT LIKE N'%UPDATE STATISTICS %COMMANDLOG%'
) BEGIN
--put together subject
SET @EmailSubject = N'Changes to ' + @DatabaseName + N' database on ' + @@SERVERNAME + N' by ' + SUSER_SNAME()
--put command text into e-mail body
SET @EmailBody = N'<p><pre><code>' + @CommandText + N'</code></pre></p>'
--append info to e-mail body
SET @EmailBody = @EmailBody + N'<p><i>This e-mail was sent from trigger "trgEmailDbaOnMasterDatabaseObjectCreation" by an automated process to "dba@domain.com". Replies to this e-mail are not monitored.</i></p>'
--send e-mail using Database Mail default profile
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'dba@domain.com',
@body_format = 'HTML',
@body = @EmailBody,
@subject = @EmailSubject,
@exclude_query_output = 1
END
END TRY
BEGIN CATCH
END CATCH
GO