Post reply

last accessed date for each database user

  • August 28, 2009 at 12:38 am

    #103810

    Hi all,

    I need script to get the

    last accessed date for each database user

    (database level), or the last access

    of each login (SQL Server level).

    Is there a way to find out this??

    Database: SQL Server 2000

    thanks.

  • August 28, 2009 at 4:36 am

    #1046101

    ananda.murugesan (8/28/2009)

    Hi all,

    I need script to get the

    last accessed date for each database user

    (database level), or the last access

    of each login (SQL Server level).

    Is there a way to find out this??

    Database: SQL Server 2000

    thanks.

    Under the standard configuration, this is not possible.

    If you enable login auditing for succesful logins, the second part is possible by querying the SQL errorlog.

    [font="Verdana"]Markus Bohse[/font]

  • August 28, 2009 at 5:43 am

    #1046122

    thanks for your reply..

    I could tried the query as follows

    select b.name as DBName,a.login_time,a.last_batch,a.hostname,a.program_name,a.hostprocess,

    a.nt_domain,a.nt_username,a.net_address,a.net_library,a.loginame

    from sysprocesses a, sysdatabases b where a.dbid=b.dbid order by a.last_batch desc

    It was written the Info from only master database, i want getting details from all the database in one server. is there way to fetch data for all database?

    Thanks

  • August 28, 2009 at 6:47 am

    #1046154

    First, I'd like to say that the query only gives you information about the currently connected users. I thought you wanted to know the last access time for any user, even though they are not currently connected to your server.

    ananda.murugesan (8/28/2009)

    It was written the Info from only master database, i want getting details from all the database in one server. is there way to fetch data for all database?

    Thanks

    I don't understand this part. Yes, sysprocesses in SQL 2000 is a system table in the master database, but it contains information about all processes, no matter which database they're active in.

    [font="Verdana"]Markus Bohse[/font]

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

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