Post reply

Security question

  • September 14, 2006 at 11:08 am

    #61821

    MSSQL 2000

    How and if I can find the LAST DATE that a USER accessed a database or the sql server? 

    Your help is appreciated.

  • September 14, 2006 at 11:27 am

    #660476

    I believe the only way is if you were already tracking login information. The only login information is for current connections. Once the connection is broken, SQL Server doesn't care about it any more.

    -SQLBill

  • September 14, 2006 at 11:35 am

    #660481

    At my current workplace I run the following proc once a minute through SQL Agent.  Not exact science but works pretty well...

    CREATE  procedure RecordDbUtilization as

    insert into DBUtilization (db) select distinct db_name(dbid) from master..sysprocesses with (nolock)

    where db_name(dbid) not in (select db from dbutilization with (nolock)) and dbid > 6

    update dbutilization set

    dateacessed = getdate(),

    host = hostname,

    NTUser = nt_username,

    login = loginame,

    app = program_name

    from master..sysprocesses with (nolock)

    where db_name(dbid) = db

    and loginame not in ('NT AUTHORITY\SYSTEM')

    GO

  • September 15, 2006 at 5:51 am

    #660654

    Thank you guys for your help.

    Terry, I will use your procedure. Thanks

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

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