Solved

sql query

Posted on 2015-01-20
2
72 Views
Last Modified: 2015-01-27
I need a query that will give me the last login time for all databases within an instance of sql and also give me database name and only one entry per database.

Thanks!
0
Comment
Question by:jamesmetcalf74
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
login time is per LOGIN, not per database. within a session, you can then use any of the databases in that instance (given the permissions being granted to the LOGIN via either server roles or via the mapped USER in the relevant databases)
hence, in short, a login time per database is not possible.

next, the login audits are not inside the sql server, but in nt event log, and requires the login success event to be configured on the instance level. still, in the end, this will not be a SQL query, but a request to the windows event log, unless you export that one into a sql table.

another option is that any application that is doing the login is running a "log this login" procedure that stored the information then in a central table. to be implemented... could eventually be a instance wide logon trigger  (see here: http://msdn.microsoft.com/en-us/library/bb326598.aspx)

in short, the answer is: you cannot do it.
long answer: you can implement this , based on your requirements
0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
do you actually mean a user login  to a database , or are you attempting to determine when databases where "last used"...
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now