• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

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
0
jamesmetcalf74
Asked:
jamesmetcalf74
  • 2
  • 2
  • 2
2 Solutions
 
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.
0
 
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Nice.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
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.
0
 
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.
0
 
jamesmetcalf74Author Commented:
Worked like a charm.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now