Log access and account name SQL 2008

Posted on 2012-09-21
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?
Question by:billherde
    LVL 3

    Expert Comment

    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

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

    Author Comment

    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?
    LVL 3

    Accepted Solution

    You could configure login auditing for both failed and successful logins.

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

    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

    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…
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    734 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