Synonym being recreated randomly - possible to create some kind of trigger/log?

Question

We have a couple of databases with synonyms to link each other, one of the synonyms is randomly being recreate incorrectly with an additional set of square brackets in the database name. I've checked and rechecked every script we have and none seem to be the culprit.

At this point I'm trying to see if I can log any transaction which drops/creates a synonym, is it possible?

Answer 1

You could do this with a trigger, by logging the login + query executed

Create the logging table

CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
                                 LoginName varchar(200), 
                                 QueryExecuted varchar(max));

Create the trigger (If you only want to log login name + the drop statement)

CREATE TRIGGER SynonymTrigger
ON DATABASE
AFTER DROP_SYNONYM  
AS
INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted)
SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');

Testing

CREATE SYNONYM TEST
FOR dbo.test;

DROP SYNONYM TEST;

Result

SELECT * 
FROM DBO.SynonymTriggerLogging;

ID  LoginName   QueryExecuted
1   rvertongen  DROP SYNONYM TEST

If you also need the hostname, use the following table & trigger

CREATE TABLE DBO.SynonymTriggerLogging( ID int Identity(1,1) PRIMARY KEY NOT NULL,
                                 LoginName varchar(200), 
                                 QueryExecuted varchar(max),
                                 HostName varchar(200));
GO
CREATE TRIGGER SynonymTrigger
ON DATABASE
FOR DROP_SYNONYM  
AS
INSERT INTO DBO.SynonymTriggerLogging(LoginName,QueryExecuted,HostName)
SELECT ORIGINAL_LOGIN(),EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),HOST_NAME()

Also take a look at example D from this link to prevent SYNONYM's from being dropped.