Linked server issue

[sendba]

All,

In the rescent times someone in the company is dropping the linked servers. Is there a way to capture who is dropping the linked servers on a particular server. Is there any DDL triggers available.

Please let me know

Thanks

 

[hmckillop]

Hello,

I recommend running profiler. This will capture all the events you specify, including logins, stored procs executed etc. One way of checking is also to filter on specific commands such as sp_dropserver.

I think its probably the most practical and simpliest way.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[Mich]

Profiler might work, but you'd need to have it running 24/7 to catch the "perp". That or know exactly when they are going to drop the linked server.

You can use DDL triggers. Here is some good info =>

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx

-If it ain't broke, break it and make it better.

 

[sendba]

Mich,

I tried to create the DDL trigger. I used the below syntax but got an error.


CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR DROP_LINKED_SERVER
AS

select 'Delete'
GO

The error is
Msg 1084, Level 15, State 1, Procedure ddl_trig_database, Line 7
'DROP_LINKED_SERVER' is an invalid event type.



Please help.

 

[hmckillop]

That doesnt seem like a valid event.

Have a look at

http://msdn.microsoft.com/en-us/library/ms186456(SQL.90).aspx

This gives all the DDL events.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[sendba]

Hi hmckillop,

Can you please let me know which event to use for capturing Drop linked servers??

Thanks

 

[hmckillop]

Its funny, I cant find an event specified which captures the SP_addlinkedServer or sp_dropserver system stored procs which could be captured by a trigger.
I think there may be a way you can setup an event to capture this. Here is a list of trigger events which can be captured
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_STATISTICS
UPDATE_STATISTICS
DROP_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_TYPE
DROP_TYPE
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
CREATE_USER
ALTER_USER
DROP_USER
CREATE_ROLE
ALTER_ROLE
DROP_ROLE
CREATE_APPLICATION_ROLE
ALTER_APPLICATION_ROLE
DROP_APPLICATION_ROLE
CREATE_SCHEMA
ALTER_SCHEMA
DROP_SCHEMA
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_CONTRACT
DROP_CONTRACT
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE
ALTER_AUTHORIZATION_DATABASE
CREATE_XML_INDEX

As mentioned before I would use profiler and change the filter on the text data to be %sp_dropserver% using the "Audit Schema Object Access Event".
This means only these events will be captured where it alters the schema access e.g. sp_addlinkedserver or sp_dropserver.

"I'm living so far beyond my income that we may almost be said to be living apart