Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Log access and account name SQL 2008

Posted on 2012-09-21
3
Medium Priority
?
423 Views
Last Modified: 2012-09-21
We are trying to clean up some legacy programs that had/have hard coded names for connecting to SQL.  How can I get a log dump of what/who is accessing a DB to make sure we have it all clean before deleting the old username?
0
Comment
Question by:billherde
  • 2
3 Comments
 
LVL 3

Expert Comment

by:dave_tiller
ID: 38423195
This query will return all users that are connected to your instance:

SELECT login_name, COUNT(session_id) AS [session_count]
FROM sys.dm_exec_sessions WITH (NOLOCK)
GROUP BY login_name
ORDER BY COUNT(session_id) DESC OPTION (RECOMPILE);

This is provided by Glen Barry as part of his troubleshooting script with a bunch of useful queries.  I use that a lot.
0
 
LVL 3

Author Comment

by:billherde
ID: 38423238
Thank you for your response.
That will give me what is happening now only, I need to log what happens when automated processes run through the night. Is there anything in SQL logging that can gather the info?
0
 
LVL 3

Accepted Solution

by:
dave_tiller earned 2000 total points
ID: 38423484
You could configure login auditing for both failed and successful logins.

http://msdn.microsoft.com/en-us/library/ms175850.aspx

Or, you could create a logon trigger that will write the account and time to a table.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 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