sql 2012 login audits

I have a sql server with 80 databases.

management studio 2012 -> activity monitor -> Processes ->
under the drop down for database I only have about 10 db's listed.  I am assuming that these are the ones that are active at the current time.

is there a way to run a log that will log all logins to all databases on the sql instance over a period of lets say a week?
I need this to figure out which db's are still in use and which ones are not.

if there is a better way to accomplish this - please advise.
thank you
jamesmetcalf74Asked:
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.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You are slightly confusing terminology, I think, so I need to clarify.

Logins get you into the SQL instance.
Users get you into databases.

Are you trying to track people logging into the SQL instance or users logging into a database?

If the former, log at this article: http://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/ It talks about failed logins, but you can click "Both failed and successful logins" instead.
Vitor MontalvãoMSSQL Senior EngineerCommented:
under the drop down for database I only have about 10 db's listed.  I am assuming that these are the ones that are active at the current time.
Not active but in use at the current time (it's only a snapshot of what's happening in your instance).

One good way to check if a database is being used or not is to query the sys.dm_db_index_usage_stats. That DMV returns when an index was used by the last time. So if a database doesn't has any activity for long the last access date will be very old. In this blog you'll find a script that you can use.

Cheers

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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nice.
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

jamesmetcalf74Author Commented:
Hi Vitor.  i ran the script in the link to the blog and it seemed to work well.  in the output, if last_access = "null",
what could i infer from that?  does that mean the last access was longer than a certain time parameter.?
the output has 2 columns (name, last_access)
if last access returned value = null...what should i infer from that?  that it has never been accessed?
all the ones that returned a date/time are very recent... ie... within a day.
Vitor MontalvãoMSSQL Senior EngineerCommented:
It only counts since last time the SQL Server started, so NULL means that no connection was made since last restart.
jamesmetcalf74Author Commented:
Worked like a charm.
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 2008

From novice to tech pro — start learning today.