Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to query db user access history Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 5:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 10:23 PM
Points: 1, Visits: 14
Hello, i've been trying to find out user access history on a particular DB. I used a query which returns the last accessed date of the database however the problem is that it doesnt specify who the user was and also only returns information from the last reboot onwards.

What i'd like to get is at least the last 3-5 times the DB was accessed and by whom. This would allow me to determine whether the DB is still in use or whether I can decommission it. Any assistance is greatly appreciated. fyi the query i've been using is listed below. Thanks

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 IN ('testdb')
ORDER BY 2
Post #1521715
Posted Wednesday, December 11, 2013 3:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:49 AM
Points: 14,802, Visits: 27,281
Unless you've set up extended events (or a server-side trace) to capture that type of information, there's no way to get it just by querying SQL Server. It's not stored in a meaningful way within the system.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1521796
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse