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.
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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/
0
pma111Author Commented:
>Audit Login events

Is there a way to query if this is enabled.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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?
0
pma111Author Commented:
2012
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.