Solved

sql 2012 login audits

Posted on 2014-11-06
6
151 Views
Last Modified: 2014-11-12
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
0
Comment
Question by:jamesmetcalf74
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 200 total points
Comment Utility
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.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 300 total points
Comment Utility
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Nice.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:jamesmetcalf74
Comment Utility
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.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
It only counts since last time the SQL Server started, so NULL means that no connection was made since last restart.
0
 

Author Closing Comment

by:jamesmetcalf74
Comment Utility
Worked like a charm.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

743 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

17 Experts available now in Live!

Get 1:1 Help Now