SQL Server - When Last Datbase access on SQL Server 2000

Asked By prathap chowdary on 19-Mar-12 07:46 AM
Hi,

How can i know last database access in sql server 2000 databases.
And please let me know how can i know last login using.


Please help me.....

Regards,
Prathap.
Jitendra Faye replied to prathap chowdary on 19-Mar-12 07:53 AM
Use this command-


SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
 (SELECT
 DB_NAME(database_id) DatabaseName
 , last_user_seek
 , last_user_scan
 , last_user_lookup
 , last_user_update
 FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
 (LastAccessDate FOR last_user_access IN
 (last_user_seek
 , last_user_scan
 , last_user_lookup
 , last_user_update)
 ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY 2

Refer this link-

http://dinesql.blogspot.com/2010/05/when-was-my-sql-server-database-last.html
Somesh Yadav replied to prathap chowdary on 19-Mar-12 07:57 AM
You get the last access after the reboot only...what happens some accessed the database but later you restarted the sql and no one logged in...in this case you get null means it is incorrect result...

If you want to do for remote servers you can use either BCP/OSQL...
prathap chowdary replied to Jitendra Faye on 19-Mar-12 08:37 AM
hi that query is working for SQL SERVERS 2005 and 2008.

But i want sql server 2000.

Please if you have any technique.....please provide me

Regards,
Prathap.
Devil Scorpio replied to prathap chowdary on 19-Mar-12 03:06 PM
The simple way to capture last login by turning on the auditing

SQL Server can audit logins. Read more detail http://msdn.microsoft.com/en-us/library/ms175850.aspx and http://technet.microsoft.com/en-us/library/dd277388.aspx

I do not think you can track login usage unless the auditing is turned on.

You can try this to find out when it was last used:

SELECT accdate, name FROM master.dbo.syslogins

OR

By using LOGON triggers available since SQL Server 2005

USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
    CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'login_test') > 3
    ROLLBACK;
END;

refer the website for more info regrding LOGON triggers
http://msdn.microsoft.com/en-us/library/bb326598.aspx