7

Actually, one of the critical jobs failed while executing.

In the error message it was found that the failure is because it was missing a stored procedure.

Now how do I find out when was the stored procedure was affected by the user. How do I find out which user did it and when he did it?

srihari
  • 193
  • 1
  • 1
  • 3
  • What type of DB is this? –  Sep 25 '09 at 19:36
  • and when you find out, give'em thirty lashes! unless it was you of course! –  Sep 25 '09 at 20:00
  • @srihari - if you associate your stackoverflow and servefault accounts the question will be assigned to you. Failing that contact team@stackoverflow.com – ChrisF Sep 27 '09 at 14:10

6 Answers6

11

You get the administrative trace:

select * from fn_trace_getinfo(NULL)
where property=2
and traceid = 1

The you look into the administrative trace for events of class 47 Object:Deleted Event Class on object types 8727 Stored Procedure:

select * from fn_trace_gettable('....trc', -1)
where EventClass = 47
and ObjectType=8727

The administrative trace is periodically recycled and about 4-5 traces are kept, you should use the name of the oldest trc file still present.

If the procedure is critical then the DBA should had make sure only authorized personel can modify it or drop it. And it should had in place auditing of schema changes. This is not the fault of whoever dropped the procedure, but entirely the DBA fault.

Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • Thank you Remus for your advise I will follow the procedure you have given and will update you. –  Sep 25 '09 at 20:01
  • Remus My access permissions are not sufficient to execute this query. I will give this information the person who can do the needful in my team. Any way thanks for your valuable suggestion. –  Sep 25 '09 at 20:35
  • 1
    spoken like a true consultant, it's always the DBA at fault, bwahaha – Nick Kavadias Sep 28 '09 at 13:10
  • This was helpful to me after I changed the ObjectType from 8727 to 8272 https://msdn.microsoft.com/en-us/library/ms180953.aspx [edit: added link] – Dave Kennedy Dec 20 '16 at 20:21
7

There is no way to find out this info by default because SQL Server doesn’t support this. If your database is in full recovery mode you can try to read transaction log and see when DROP PROCEDURE statement was executed. Unfortunately there is no easy way to do this.

You can try using some of the third party tools such as ApexSQL Log or Quest Toad but even with these tools I’m not sure you’ll be able to find out the username of who did this.

Another option you can try is to check out fn_dblog function and see if you can make any use of that. Problem here is that this function is not well documented.

JdMR
  • 201
  • 2
  • 2
2

I think you're asking the wrong question here.

Why on earth is people you're not trusting given enough privileges in your database to actually delete that sproc in the first place? That's the question you need to ask.

It's like trying to find out who robbed your house after you left the key out on the porch.

Lasse V. Karlsen
  • 385
  • 5
  • 14
  • 1
    Lasse I can understand what u r expressing.The thing here is unexpectedly the stored procedure got deleted and we are assigned to find out who is the cause.There should be some solution to find out this.since I dont know to how to approach I raised this question. –  Sep 25 '09 at 19:58
  • Yes, but your still want to catch the bugger if you can right? –  Sep 25 '09 at 20:04
  • Well, it depends. The bugger here is you as well. If you left the key out on the porch, you're part of the crime, even if you don't like the results. Yes, you should probably invest time in finding out who did this, but you might end up finding out that it was an accident. An accident that was easily avoided. Just don't leave the keys out. – Lasse V. Karlsen Sep 25 '09 at 20:08
  • This is spot on. You should already know who can do this and it should be a short list. Beyond that it doesn't matter because you end up in a "never attribute to malice what can be explained by ignorance" problem. Set appropriate permissions, restore the proc and move on. – Austin Salonen Sep 25 '09 at 20:34
2

Assuming an "average" default installation of SQL Server, sitting down at your server right now, you will not be able to determine this information. By default, SQL does not log or track this kind of activity.

(There are a number of ways you can log this information (DDL triggers), but that doesn't help you now--it would only help with future activity.)

Chris mentioned going reviewing the transaction log and extracting what information is present there. This would work, but SQL 2005 does not provide any "native" functionality for sifting through transaction logs. You would need a third-party tool to do so. And that only applies for as long as that data is in the transaction log; if the database recovery mode is set to "simple", that data will be wiped from the log--sooner rather than later. (If your database is actively used, it may already be gone.)

Remus Rusanu outlined how to query the system trace. Very cool, I'm upvoting that one! As he said, this too has a limited shelf life--you should probably make copies of those files now before they get overwritten.

If the above tactics are not possible, restoring and reviewing backups might track down when it occured. This again depends on your recovery mode and whatever backup files you have. If you can do point-in-time recoveries on transaction log backups, you should be able to get a pretty close estimate on when it was dropped; If you only have complete or differential backups, you'll get less precision (e.g. was there in the 1:00pm backup, was not in the 2:00pm backup, must have been dropped between 1 and 2.)

As for who dropped it (or rather, via which SQL login was it dropped), unless you've got some intentionally configured process installed and running, I do not believe you can extract that information. A starting point would be to determine who (rather, which logins) could perform the drop, and go from there. Is your SQL installation configured to log succesful logins in the Windows Event logs? Is the domain set to track domain logins? ...though neither will help if SQL authentication was involved.

It might not be possible, but you might be able to work up some reasonable guesses. Good luck!

Philip Kelley
  • 253
  • 2
  • 9
0

You should be able to go back through a transaction log and be able to at least find out when that procedure was dropped from the database. As to who the users was, you might be able to see who was logged in to the system at that time, and be able to narrow it down somewhat.

Hope this helps some.

Chris
  • 810
  • 1
  • 7
  • 10
  • 1
    With a 3rd party tool. It's not a standard feature. – gbn Sep 25 '09 at 19:41
  • how to do this where will these logs stored(at which path).will there be any table that will be populated with this kind of data. –  Sep 25 '09 at 19:44
  • The DB is MS SQL Server 2005 –  Sep 25 '09 at 19:45
  • No, there is no "history" table to find this info by default. –  Sep 25 '09 at 19:46
  • Depends on the recovery model and how often log backups are done. If the log is backed up every 15 minutes and the proc was dropped 3 days ago, the log records for the drop will be long gone. – GilaMonster Sep 26 '09 at 08:09
0

You can use below script to identify suspect user:

SELECT 
    te.name AS eventtype
    ,t.loginname
    ,t.spid
    ,t.starttime
    ,t.objectname
    ,t.databasename
    ,t.hostname
    ,t.ntusername
    ,t.ntdomainname
    ,t.clientprocessid
    ,t.applicationname  
FROM sys.fn_trace_gettable
(
    CONVERT
    (VARCHAR(150)
    ,(
        SELECT TOP 1 
            value
        FROM sys.fn_trace_getinfo(NULL)  
        WHERE property = 2
    )),DEFAULT
) T 
INNER JOIN sys.trace_events as te 
    ON t.eventclass = te.trace_event_id 
WHERE eventclass=164
Sven
  • 97,248
  • 13
  • 177
  • 225
Anvesh
  • 674
  • 7
  • 3