Solved

sql 2012 login audits

Posted on 2014-11-06
6
154 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
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 46

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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 46

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PowerShell Default Ports 3 27
Managing Columnstore Indexes 2 19
who removed AD Domain ID 9 28
vmdk greater than 2TB 2 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

863 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

28 Experts available now in Live!

Get 1:1 Help Now