Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

DDL Trigger Notification for SQL Server Linked Server Changes


By:   |   Updated: 2019-03-22   |   Comments   |   Related: More > 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.
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


Last Updated: 2019-03-22


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools