DDL Trigger Notification for SQL Server Linked Server Changes

By: Daniel Farina | Updated: 2019-03-22 | Comments | Related: > Triggers

Problem

In this tip I will show how you to create a notification when there is a configuration change to SQL Server Linked Servers.

Solution

We as DBAs have to be aware of what is going on with our SQL Server instances, but as you know it is a very complex task because we don't have enough time to pay full attention to all our instances.

This makes us rely on third party tools and automated scripts for monitoring different facets of our database servers. We have monitoring applications for different things, like performance metrics, resource usage, and space consumption but also for monitoring server and instance availability. What we don't have are tools that let us know about the mistakes our DBA team can make, like for example changing the configuration of a Linked Server. As a consequence, we usually create scripts to send notifications on a case by case basis. In this case, we are going to create a script that sends a notification if a SQL Server Linked Server is dropped, changed or created.

Know when a SQL Server Linked Server is Dropped, Altered or Created

There is a feature of SQL Server which you probably don't use everyday that can provide us with the information about configuration changes on a SQL Server Linked Servers. I am referring to DDL (Data Definition Language) triggers. This type of trigger fires in response to changes on the database structure like when you create a table or a login. Additionally this type of trigger can be created at the server level (i.e. fire on events that occur anywhere on the current server).

Inside a DDL trigger you can get information about the firing event by using the EVENTDATA function. This function returns an XML formatted string of XML data type. In order to parse this XML string you have to know its schema definition. Fortunately we can find the xsd file on the SQL Server Tools installation directory at C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2006\11\events where 140 is the version number of SQL Server. Notice that if you have a 32 bit version of SQL Server you don't need to put the (x86) in Program Files folder. You can also find this xsd file as well as all other schema definition files related to SQL Server at the Microsoft SQL Server XML Schemas web page http://schemas.microsoft.com/sqlserver/.

To get the schema definition for any specific event, you have to search the file for the Complex Type EVENT_INSTANCE_<event_type>. For example, if you want to extract the schema for the CREATE_LINKED_SERVER event, then you have to search the schema for EVENT_INSTANCE_ CREATE_LINKED_SERVER.

The next table shows the elements of CREATE_LINKED_SERVER, ALTER_LINKED_SERVER and DROP_LINKED_SERVER.

Element Name	Type	Description
EventType	STRING	The type of the event that fired the trigger, for example CREATE_LINKED_SERVER
PostTime	STRING	The time of the event.
SPID	INT	The session context on which the DML trigger was fired.
ServerName	STRING	The server name
LoginName	STRING	The credentials used to authenticate to SQL Server
ObjectName	STRING	The name of the object referenced by the event that fired the trigger. For example, if the Event is CREATE_LINKED_SERVER then ObjectName would be the new linked server name.
ObjectType	STRING	The type of the object being referenced. For example, if the Event is CREATE_LINKED_SERVER then ObjectType would be LINKED SERVER.
Parameters	XML	The parameters of the command that fired the trigger.
TSQLCommand	XML	The command that fired the trigger.

The CREATE_LINKED_SERVER_LOGIN and DROP_LINKED_SERVER_LOGIN events schema definition adds two additional elements, which I show in the table below.

Element Name	Type	Description
TargetObjectName	STRING	The user name on the remote server for login mapping.
TargetObjectType	STRING	This is always "LOGIN"

Setup Environment to Capture SQL Server Linked Servers Change

Before going on with the creation of the trigger, I want to include an additional step which I think will ease your understanding. We are going to create a server trigger to catch the CREATE_LINKED_SERVER_LOGIN, DROP_LINKED_SERVER_LOGIN, CREATE_LINKED_SERVER, ALTER_LINKED_SERVER and DROP_LINKED_SERVER events, but instead of sending the notification we will save the output of the EVENTDATA() function into a table on a user database. Due to the fact that this will be a server trigger it will be stored in the master database no matter the context on which database you create it.

In order to set up our test environment, first we need to create a database SampleDB that will contain the table in which we will store the output of the EVENTDATA() function.

USE Master;
GO

CREATE DATABASE [SampleDB]
 ON  PRIMARY 
( NAME = N'HR', FILENAME = N'E:\MSSQL\SampleDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'HR_log', FILENAME = N'E:\MSSQL\SampleDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

The next script will create a table named LogLinkedServerChange in the SampleDB database.

USE sampleDB
GO

DROP TABLE LogLinkedServerChange
GO

CREATE TABLE LogLinkedServerChange
    (
      LogDataID INT NOT NULL IDENTITY(1, 1),
      EventType NVARCHAR(100),
      PostTime DATETIME,
      SPID INT,
      ServerName NVARCHAR(100),
      LoginName NVARCHAR(100),
      ObjectName NVARCHAR(100),
      ObjectType NVARCHAR(100),
      [Parameters] NVARCHAR(1000) NULL,
      TargetObjectName NVARCHAR(100) NULL,
      TargetObjectType NVARCHAR(100) NULL,
      TSQLCommand NVARCHAR(1000) 
      PRIMARY KEY CLUSTERED ( LogDataID )
    )
GO

The next script creates a trigger named TR_LogLinkedServerChange that will insert a row in the table LogLinkedServerChange each time it's fired.

USE [master]
GO

CREATE TRIGGER TR_LogLinkedServerChange ON ALL SERVER
    FOR CREATE_LINKED_SERVER, 
        ALTER_LINKED_SERVER, 
        DROP_LINKED_SERVER,
        CREATE_LINKED_SERVER_LOGIN, 
        DROP_LINKED_SERVER_LOGIN
AS
    DECLARE @data XML  
    SET @data = EVENTDATA()  
    SELECT  @data

    INSERT  sampledb.dbo.LogLinkedServerChange
            ( EventType ,
              PostTime ,
              SPID ,
              ServerName ,
              LoginName ,
              ObjectName ,
              ObjectType ,
              [Parameters] ,
              TargetObjectName ,
              TargetObjectType ,
              TSQLCommand
            )
    VALUES  ( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/Parameters)[1]', 'NVARCHAR(1000)') ,
              @data.value('(/EVENT_INSTANCE/TargetObjectName)[1]','nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(1000)')
            );  
GO

As you can see, the only difficult part of understanding the code above relies on knowing how to use XML variables to access its members. If that is your case, at the end of the tip I will include a link to a tip that will help you better understand the XML data type usage in SQL Server.

Additionally, we must enable the trigger.

ENABLE TRIGGER TR_LogLinkedServerChange ON ALL SERVER
GO

In order to test the trigger I will create a linked server to a server named SQL-B. The next code section contains the code generated by SQL Server Management Studio to create the linked server.

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'SQL-B', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SQL-B', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQL-B', @locallogin = NULL , @useself = N'True'
GO

The next two screen captures show the contents of the LogLinkedServerChange table after creating the Linked Server. I broke the output into two screen shots to make it easier to read.

This image shows the output of EVENTDATA() function that we stored in the LogLinkedServerChange table.

As you can see, the table contains one row for each execution of any of the system stored procedures involved in the creation of the linked server.

Send Email Notification when SQL Server Linked Servers Change

Now in order to send a notification by email we have to modify the previous script to send an email instead of writing to a table. As you may see in the code below, I have added a table variable to insert the EVENTDATA() elements and then I concatenated the elements in a NVARCHAR variable using HTML format. Finally I send the email using the sp_send_dbmail stored procedure.

Create TRIGGER TR_LogLinkedServerChange ON ALL SERVER
    FOR CREATE_LINKED_SERVER, 
        ALTER_LINKED_SERVER, 
        DROP_LINKED_SERVER,
        CREATE_LINKED_SERVER_LOGIN, 
        DROP_LINKED_SERVER_LOGIN
AS

DECLARE @header NVARCHAR(4000) = '<HTML>
<BODY>
<h1 align="center">Linked Server Alert</h1>
<TABLE>
 <TR>
 <TH bgcolor="#4f81bd">EventType</TH>
 <TH bgcolor="#4f81bd">PostTime</TH>
 <TH bgcolor="#4f81bd">SPID</TH>
 <TH bgcolor="#4f81bd">ServerName</TH>
 <TH bgcolor="#4f81bd">LoginName</TH>
 <TH bgcolor="#4f81bd">ObjectName</TH>
 <TH bgcolor="#4f81bd">ObjectType</TH>
 <TH bgcolor="#4f81bd">Parameters</TH>
 <TH bgcolor="#4f81bd">TargetObjectName</TH>
 <TH bgcolor="#4f81bd">TargetObjectType</TH>
 <TH bgcolor="#4f81bd">TSQLCommand</TH>
 </TR>'

DECLARE @bottom NVARCHAR(1000)= '</TABLE></BODY></HTML>'

DECLARE @Message NVARCHAR(MAX)

DECLARE @Temp  TABLE 
    (      
      EventType NVARCHAR(100) ,
      PostTime DATETIME ,
      SPID INT ,
      ServerName NVARCHAR(100) ,
      LoginName NVARCHAR(100) ,
      ObjectName NVARCHAR(100) ,
      ObjectType NVARCHAR(100) ,
      [Parameters] NVARCHAR(1000) NULL ,
      TargetObjectName NVARCHAR(100) NULL ,
      TargetObjectType NVARCHAR(100) NULL ,
      TSQLCommand NVARCHAR(1000) 
    )

    DECLARE @data XML  

    SET @data = EVENTDATA()  

    INSERT  @Temp
            ( EventType ,
              PostTime ,
              SPID ,
              ServerName ,
              LoginName ,
              ObjectName ,
              ObjectType ,
              [Parameters] ,
              TargetObjectName ,
              TargetObjectType ,
              TSQLCommand
            )
    VALUES  ( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/Parameters)[1]', 'NVARCHAR(1000)') ,
              @data.value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/TargetObjectType)[1]', 'nvarchar(100)') ,
              @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(1000)')
            );  

SELECT @Message = @header+ '<TR>' +
'<TD>' + [EventType] + '</TD>' +
'<TD>' + CAST([PostTime] as  NVARCHAR(100)) + '</TD>' +
'<TD>' + CAST([SPID] as  NVARCHAR(100)) + '</TD>' +
'<TD>' + [ServerName] + '</TD>' +
'<TD>' + ISNULL([LoginName], '') + '</TD>' +
'<TD>' + ISNULL([ObjectName], '') + '</TD>' +
'<TD>' + ISNULL([ObjectType], '') + '</TD>' +
'<TD>' + ISNULL([Parameters], '') + '</TD>' +
'<TD>' + ISNULL([TargetObjectName], '') + '</TD>' +
'<TD>' + ISNULL([TargetObjectType], '') + '</TD>' +
'<TD>' + ISNULL([TSQLCommand], '') + '</TD>' +
'</TR>'+ @bottom 
FROM @temp


EXEC msdb..sp_send_dbmail @profile_name = 'DBAMail', -- sysname
    @recipients = '[email protected]', -- varchar(max)
    @subject = N'Linked Server Alert', -- nvarchar(255)
    @body = @Message, -- nvarchar(max)
    @body_format = 'HTML'
GO

Additionally, we must enable the trigger.

ENABLE TRIGGER TR_LogLinkedServerChange ON ALL SERVER
GO

On the next screen capture you will see how the alert looks in your email inbox.

Email from the trigger based on a change to a SQL Server Linked Server.

Next Steps

Read my previous tip about getting started with triggers.
If you need to refresh concepts about querying XML data type variables you will find this tip useful: Basic SQL Server XML Querying.
As a prerequisite to send alerts by email you must have configured Database Mail. If you don't have it set up then the next tip will guide you: Setting up Database Mail for SQL Server.
Additionally you will need to create a linked server on your test environment. To do so you can use this tip as a guide: Pointing a SQL Server Linked Server to Different Data Sources.
You can take a look at my previous tip Create SQL Server Disk Space Report for All Servers where I also used this HTML template to create a report in HTML format.
If you need to check for linked server availability I suggest you read this tip: Script to check all your linked server connections for SQL Server.
Stay tuned to SQL Server Triggers Tips Category for more tips and tricks using triggers.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-03-22