Finding the Culprit for deleting Linked Server

Question

Post reply

Finding the Culprit for deleting Linked Server

chandan_jha18

SSChampion

Points: 11330
More actions
April 13, 2011 at 6:47 am

#248857

Hi,
Someone deleted few linked servers and I need to find who did it and when? I know the time frame and I do have default traces of that period. Can someone please tell me how to read this information from the default trace or log file (using fn_dblog)?
Thanks
Chandan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I'm afraid you are out of luck, unless you have a separate DML trace running prior to the offense.

adding and dropping linked servers are not DDL operations, and do not appear in the default trace.

any linked servers that are added/deleted are in the master.sys.servers table; since the RECOVERY model on master is set to SIMPLE, the change is committed immediately, so there's no log to read.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steveb. SSC-Forever Points: 46733 More actions · Answer 2

As mentioned you can not get this info from the trace,

I would take a 'softer' approach and look at which users have the neccesary permissions to perfrom this operation - there should not be many on a production server. And then work the likely culprit from there..

chandan_jha18 SSChampion Points: 11330 More actions · Answer 3

Lowell (4/13/2011)
I'm afraid you are out of luck, unless you have a separate DML trace running prior to the offense.
adding and dropping linked servers are not DDL operations, and do not appear in the default trace.
any linked servers that are added/deleted are in the master.sys.servers table; since the RECOVERY model on master is set to SIMPLE, the change is committed immediately, so there's no log to read.

Thanks for the input. I believe any transaction will be logged to the log file irrespective of the db recovery model. Should'n then the fn_dblog function give me some information?

Lowell SSC Guru Points: 323518 More actions · Answer 4

Remember the transaction log does not retain any information about WHO performed any operation.

I cannot see it pointing to the level of detail you are looking for, like object name of a table or anything yet, but i'm still looking.

SELECT *

FROM ::fn_dblog(NULL, NULL)

where [Lock Information] like '%database_id = 1%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok actually, I believe this shows you some of the info specific to a linked server:

SELECT *

FROM ::fn_dblog(NULL, NULL)

where [allocUnitName] IN('sys.sysxsrvs.cl','sys.syslnklgns.cl')

but without a way to tie it to a user, you just know some detailed information; i don't even see a way to tie it into to get the timestamp of when it occurred.

maybe a deep analysis of the rows of data immediately above the event?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!