Post reply

SQL Object last accessed

  • January 23, 2002 at 3:24 am

    #58177

    Hi

    Is there any sp / script / tool to determine when a database object was last accessed / used.

    I see the sysobjects table has a refdate columne in it, which according to the help is 'reserved for future use'

    It would be nice to also determine which users and the date they last accessed an object.

    Any help / comments would be appreciated.

    thanks

    Steve

  • January 23, 2002 at 8:17 am

    #422499

    Can you define "last accessed". Is this the last change or the last time a select occurred against a table. Or the last time a stored procedure was executed?

    Steve Jones

    steve@dkranch.net

  • January 24, 2002 at 1:23 am

    #422578

    Thansk for responding. I have 'inherited' a really disorganized database with numerous tables and stored procs. The database is accessed by three applications via their own user login (each of these are also db_owner). Without going thru approximately 300000 lines of code, I would like to get an idea of which tables and stored procedures these users access. What I ulitmately want to do is setup roles with only the necessary rights for each of these users. The database is MS SQL 2000. The last accessed would be a nice to have i.e. when a select was made against a table or a stored proc. was last executed by a user.

    thanks

    Steven

  • January 24, 2002 at 1:54 am

    #422581

    Profiler is a great tool to collect this sort of information. We are having to do some very heavy auditing on a internally built application and Profiler has been able to show some problem spots. You're not necessarily looking for problem spots right now, but by capturing a trace and exporting to a table, you can then parse to find out how often objects are getting called.

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • January 24, 2002 at 9:37 am

    #422605

    Agree with Brian. There is no way in SQL to track the access, other than profiler. You could use a sniffer, but profiler essentially does that. you could also implement C2 security for a short time, but be careful. This will track EVERY event on the server and can result in logs that are 100s of MB or even GB very quickly.

    Steve Jones

    steve@dkranch.net

Viewing 5 posts - 1 through 5 (of 5 total)

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