Link to home
Start Free TrialLog in
Avatar of Pau Lo
Pau Lo

asked on

SQL Server last accessed information

is there an easy way to determine the last time a user account established a connection to a SQL Server instance, or directly to a database within an instance? I need to verify who has access to a SQL instance, and wanted some stats in regards to last accessed information to build my case if some do not seem to be appropriate.
Avatar of Martyn Spencer
Martyn Spencer
Flag of United Kingdom of Great Britain and Northern Ireland image

SQL server has an auditing tool that you can enable. I believe it is disabled by default, so it may not help you for past connections. Check out this link (it is a bit old, sorry): https://dba.stackexchange.com/questions/54494/sql-server-keep-track-of-all-connections-disconnections

Another link, also old is https://docs.microsoft.com/en-us/previous-versions/tn-archive/dd277388(v=technet.10)#XSLTsection123121120120

I imagine later versions of SQL server support similar, or better.
>> I need to verify who has access to a SQL instance,

Below query can give you the list of logins that have access to your SQL Server instance.
SELECT *
FROM sys.server_principals
WHERE is_disabled = 0

Open in new window


>> and wanted some stats in regards to last accessed information to build my case if some do not seem to be appropriate

Just enable Audit Login events and capture both Success and Failure events. If done, this will increase the size of your SQL Server Error Log depending upon how many login events are happening in your server.
Once captured, you can find out when the last login happened for a particular login..
https://mssqltalks.wordpress.com/2013/02/25/how-to-audit-login-to-my-sql-server-both-failed-and-successful/
Avatar of Pau Lo
Pau Lo

ASKER

>Audit Login events

Is there a way to query if this is enabled.
By default I believe it is disabled. It would be evident whether or not it is enabled when you navigate to the relevant options in your version of SQL server. What version are you using?
Avatar of Pau Lo

ASKER

2012
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial