November 17, 2007 at 11:16 am
Hello All,
Newbie here, Just wondering is there a way to track down what logged in user (mixed mode) who deleted a stored procedure? We have several developers with rights that will allow this but no one wants to admit to the mistake. Is there a log I am overlooking that records actions to database objects? Will enabling C2 auditing capture these actions?
Any assistance would be appreciated.
Thankyou
🙂
November 17, 2007 at 8:40 pm
In SQL Server 2000 there is no log for this unless you set it up. Depending on your recovery mode, you may be able to use a tool like Apex SQL Log or Red Gate's log tool examine the transaction log and see if you can garner any information out of that. Otherwise, the damage is done and there is no record of it.
With respect to C2 compliance mode. Yes, it would catch it. But unless you have a reason to go C2 compliance mode, it's overkill and it will affect performance. Plus, you'll have to deal with all the disk space the traces are going to take up. A better option is to build a server side trace which monitors object creation and deletion. You can build such using SQL Profiler and ask it to generate the server side trace for you. You could then take the trace, encapsulate it in a stored procedure in the master database, and use sp_procoption to set the stored procedure to execute whenever SQL Server is started. That would ensure a continual trace.
K. Brian Kelley
@kbriankelley
November 19, 2007 at 4:27 am
is this possible with sql server 2005 ?
November 19, 2007 at 4:50 am
In SQL 2005 you can define DDL_Triggers and Event_notifications to catch these type of actions. See BOL for details.
But even if you haven't defined any of those the default trace and the "Schema changes History" report in SSMS will show who deleted the table at least if you don't wait too long.
Markus
[font="Verdana"]Markus Bohse[/font]
November 19, 2007 at 6:05 am
MarkusB (11/19/2007)
In SQL 2005 you can define DDL_Triggers and Event_notifications to catch these type of actions. See BOL for details.But even if you haven't defined any of those the default trace and the "Schema changes History" report in SSMS will show who deleted the table at least if you don't wait too long.
This is one of those little publicized features of SQL Server 2005. SQL Server 2005 automatically starts up a trace when SQL Server starts which audits for schema manipulation. You can find the trace files in your LOG directory. The default location would be:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
They'll be named log_###.trc.
K. Brian Kelley
@kbriankelley
November 29, 2007 at 2:38 pm
I would suggest using a 3rd party tool to catch these event. I currently use Lumigent monitoring system.
November 30, 2007 at 3:11 am
I use a 3rd party tool for both SQL 2000 and 2005 also, Idera SQL Compliance Manager
March 19, 2008 at 2:05 pm
Is there any way to turn this trace off?
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
March 19, 2008 at 2:55 pm
Nevermind:
sp_configure 'default trace enabled', 0
go
reconfigure
go
Take care,
Bert
"Speculations? I know nothing about speculations. I'm resting on certainties. I know that my Redeemer lives, and because He lives, I shall live also." - Michael Faraday
December 1, 2008 at 9:45 am
HI,
If we check the 'Schema Changes Histor' report we find only login name and user name column.
Is there any way where we could even get hostname or the ip address of the machine from where the schema changes has been done.
Would be great help.
Thanks.
December 1, 2008 at 10:52 am
The report is based on the default trace.
If you open the trace file(s) you can see the hostname
[font="Verdana"]Markus Bohse[/font]
December 2, 2008 at 7:30 am
thanks
January 5, 2009 at 5:29 am
Hi
I m sorry to distrub u...
i m getting a problem..
when i m connecting a server through the local machine , it is not coneected
so any one please help me ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply