May 13, 2009 at 12:32 pm
Hi,
I have a sql server prod database. This is being accessed by both admin and the application team.
The issue is someone added a new datafile onto primary and I wanted to find out who did it.
I have already retrieved the list of objects in this data file but that does not tell me who added the file.
How can i find this out?
Murali
May 13, 2009 at 12:57 pm
you may be able to find this info by reading the transaction log.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 13, 2009 at 1:15 pm
I believe you can find that out using the Default Trace. The only issue is that you might be too late as the Default Trace only keeps up to 100MB of activity so on a busy server that can roll over fairly quickly.
Here's a link to a script[/url] that will get you started on querying the default trace.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2009 at 1:35 pm
Thanks guys for such quick replies.
1. I have never mined a transaction log. If someone can provide me some examples,
that will be great.
2. It is good to know who has accessed the server. How do I know who did the ddl
and what ddl was done other than turning on c2 level auditing
May 13, 2009 at 1:49 pm
The script I linked to was just to give you an idea of how to query the default trace. Based on my quick testing and research the default trace will report the addition of a database file as Object:Altered event (eventclass = 164) with a Begin and Commit (eventsubclass = 0 and 1 respectively) with an ObjectType of 16964 (database). Unfortunately this criteria applies to any change made to an existing database. You will get an idea of who has made changes to the database in question though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2009 at 2:08 pm
You should also review who has the ability to do this. It should not be many people.
You could broadcast that you are about to undertake this investigation and express concerns. Maybe someone will come forward without you spending any time on this.
May 13, 2009 at 2:34 pm
It's always easy with hindsight - if you have builtin admins as sysadmin I doubt you'll ever find out. I usually try to remove builtin admins ( with care on a cluster ) and limit syadmins - that's really the only way to handle this otherwise it's akin to the wild west.
As to mining the tran log - you can buy utilities to do this - haven't done so for a while, many demos don't allow you to use againsta user database, only samples.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply