Post reply

User deleted from the database

  • November 3, 2009 at 9:49 am

    #216962

    Hi all,

    A user and its associated certificates are deleted from the database 15 days ago. Now we realized it and tried to create those stuff again. But is there a way to find out when the user and the certificate got deleted. We don't have the backup of that particluar day but we have backups from 2 days after the incident took place.

    Any input is greatly appreciated.

    Thanks,

    Venu

  • November 4, 2009 at 5:09 pm

    #1075096

    Hi,

    You might try the default trace - there is a chance it was not disabled. Check this query:

    select * from sys.traces where is_default = 1

    If you have this trace, there might be a chance you will find something in it using this query or similar:

    select * from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_86.trc', 2)

    where textdata is not null

    order by TransactionId desc

    Just enter proper file name, narrow the date range and you might need to increase number of accessed files of the trace in the second parameter of the fn_trace_gettable.

    Regards

    Piotr

    ...and your only reply is slàinte mhath

  • November 5, 2009 at 11:22 am

    #1075525

    Thanks alot for your response!!

    With the 1st statement I got the trace path info. Now I used that path in the second statement and tried to get information.

    It is giving me only those select statements (the same 2nd statement) I run on that server but nothing else. I tried to delete a certificate and then user associated with it from a database and ran the 2nd statement again still it didn't give me any additional information.

    So is it possible to get the information like when the certificate or user are deleted? Will it be saved anywhere in the SQL server like the Logs or the system databases?

    Thanks in advance!

Viewing 3 posts - 1 through 2 (of 2 total)

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