none
how to find the which user connect to database at last time ?

    Question

  • Hi All,

    i have received a user request that is " which user connected to database at last time and at the same time who  updated the data at particular time"

    we needs  a script for this ..

    please post if you have any idea about on this ......

    Thanks


    RAM

    Tuesday, May 29, 2012 2:39 PM

Answers

  • This is a bit challenging when you implement your own solution. This can be done via logon triggers, SQL Trace, SQL Server Audit (if you are on SQL Server 2008 and higher), or third party tools like SQL Compliance Manager from Idera.

    What is more challenging here is the process and policy that you want to put in place to capture these information using the technology you have chosen. With a high volume, high transaction database, you will end up capturing massive amount of data if you want to audit everything. You need to define the process first to assist you in making decision on what technology to use

     

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn


    Tuesday, May 29, 2012 3:25 PM
    Moderator
  • In addition to what Edwin has said:

    I couldn't tell if you were looking for how to do this for the FUTURE or how you could check this NOW. IF the question is this already happened and you would like to find this now, the EASIEST and SHORTEST answer is... you can't. The LONG answer is, depending on a whole bunch of different things you may be able to check the plan cache and find AN answer but not maybe THE answer. The reason I typed it like that was because plans can be pushed out of cache in different ways (aged out, memory pressure, ALTER DATABASE, etc) and thus your correct answer is already flushed.

    -Sean


    Sean Gallardy, MCC | Blog

    Tuesday, May 29, 2012 3:37 PM
    Answerer

All replies

  • This is a bit challenging when you implement your own solution. This can be done via logon triggers, SQL Trace, SQL Server Audit (if you are on SQL Server 2008 and higher), or third party tools like SQL Compliance Manager from Idera.

    What is more challenging here is the process and policy that you want to put in place to capture these information using the technology you have chosen. With a high volume, high transaction database, you will end up capturing massive amount of data if you want to audit everything. You need to define the process first to assist you in making decision on what technology to use

     

    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn


    Tuesday, May 29, 2012 3:25 PM
    Moderator
  • In addition to what Edwin has said:

    I couldn't tell if you were looking for how to do this for the FUTURE or how you could check this NOW. IF the question is this already happened and you would like to find this now, the EASIEST and SHORTEST answer is... you can't. The LONG answer is, depending on a whole bunch of different things you may be able to check the plan cache and find AN answer but not maybe THE answer. The reason I typed it like that was because plans can be pushed out of cache in different ways (aged out, memory pressure, ALTER DATABASE, etc) and thus your correct answer is already flushed.

    -Sean


    Sean Gallardy, MCC | Blog

    Tuesday, May 29, 2012 3:37 PM
    Answerer
  • http://manvendradeosingh.blogspot.com/2011/01/to-find-out-no-of-traces-running-on.html

    I think You can check your default trace file to check who was connected last time to your dbs. Find the link to find out the no of traces on your box. Trace ID 1 is the default trace id. Below code will let you know the no of traces running and their location.

    SELECT * FROM fn_trace_getinfo(default);
    GO

    Thanks & Regards Manvendra Deo Singh My Blog:- http://manvendradeosingh.blogspot.com/

    Thursday, May 31, 2012 1:47 PM