SQL Server last accessed information

pma111
pma111 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
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.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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/

Author

Commented:
>Audit Login events

Is there a way to query if this is enabled.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
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?

Author

Commented:
2012
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Is there a way to query if this is enabled.

By default it would be enabled to capture failed logins only..
To change it, follow the steps below:
1. In SSMS, Right Click your Server and Choose Properties.
2. Click on Security
3. Under Login Auditing, Choose "Both failed and successful logins"
4. Click on OK.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial