locked
How to find the user who is dropping the Linked server RRS feed

  • Question

  • Hi Everyone,

    We have couple of linked server in a sql server. Its being dropped continuously by some user one in the server.  

    We are not sure how to find\track the user\login who is dropping the linked server. 

    Could you please some one help me in providing a sql script to track the change happening in the existing linkeserver in sql.

    Thanks in advance. 
    Tuesday, July 18, 2017 5:48 PM

Answers

  • You need to create a ddl_trigger on your master database.
    create table logme(dtstamp datetime, Event_Data XML)
    GO
    IF EXISTS (SELECT * FROM sys.server_triggers  
        WHERE name = 'ddl_trig_database')  
    DROP TRIGGER ddl_trig_database  
    ON ALL SERVER;  
    GO  
    CREATE TRIGGER ddl_trig_database   
    ON ALL SERVER   
    FOR DROP_LINKED_SERVER
    AS   
    	insert into logme	
        SELECT getdate(), EVENTDATA() 
    GO  
    
     


    Tuesday, July 18, 2017 6:13 PM
  • This should help you to figure out who did it.

     
    select dtstamp, Event_Data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname'),
      Event_Data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(2000)')
      From logme
    
     
      

    • Marked as answer by jack619_m Wednesday, July 19, 2017 6:24 AM
    Tuesday, July 18, 2017 6:19 PM

All replies

  • You need to create a ddl_trigger on your master database.
    create table logme(dtstamp datetime, Event_Data XML)
    GO
    IF EXISTS (SELECT * FROM sys.server_triggers  
        WHERE name = 'ddl_trig_database')  
    DROP TRIGGER ddl_trig_database  
    ON ALL SERVER;  
    GO  
    CREATE TRIGGER ddl_trig_database   
    ON ALL SERVER   
    FOR DROP_LINKED_SERVER
    AS   
    	insert into logme	
        SELECT getdate(), EVENTDATA() 
    GO  
    
     


    Tuesday, July 18, 2017 6:13 PM
  • This should help you to figure out who did it.

     
    select dtstamp, Event_Data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname'),
      Event_Data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(2000)')
      From logme
    
     
      

    • Marked as answer by jack619_m Wednesday, July 19, 2017 6:24 AM
    Tuesday, July 18, 2017 6:19 PM