Solved

sql 2012 login audits

Posted on 2014-11-06
6
169 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 200 total points
ID: 40426203
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 49

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 40426264
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
ID: 40426279
Nice.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:jamesmetcalf74
ID: 40428485
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 49

Expert Comment

by:Vitor Montalvão
ID: 40428499
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
ID: 40438135
Worked like a charm.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

730 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