none
sql server linked server was deleted. How to find when and who deleted the linked server? RRS feed

All replies

  • Hi bestrongself,

    The first thing that is clear is that only users with the sysadmin role have permission to delete the Linked Server.

    Secondly, the event of deleting the Linked server will not be tracked in the default tracking or error log file, so if you did not run a separate DML tracking or configure any content that captures the event before the deletion, It will be difficult to find out who deleted the linked service at what time.

    You can create a trigger with the following script to ensure that detailed information will be recorded when the Linked server is deleted in the future.
    ---------------------------------------------------------------------------------------------------------------------
    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 

    --use the below to get how did it
    select dtstamp, Event_Data.value('(/EVENT_INSTANCE/LoginName)[1]','sysname'),
    Event_Data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(2000)')
    From logme
    -------------------------------------------------------------------------------------------------------------------------

    Hope this could help you.
    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 3, 2019 5:40 AM