Post reply

sa account login_time field

  • February 7, 2018 at 5:39 am

    #329089

    Hi all, I have a query which originated from external auditors ...
    They wanted to know when alst the sa account was used (i'm using the login_time field) from the below quer
    SELECT MAX(login_time) AS [Last Login Time], login_name [Login] 
    FROM sys.dm_exec_sessions FROM sys.dm_exec_sessions
    GROUP BY login_name; GROUP BY login_name;
    the sa account seems to update roughly every 2 minutes, I want to  know is this an internal process or is it being used?
    I already changed the password and no one complained so it confirmed to me that no one is using or knows the password. there are no login failed messages in our error log as well.
    This is why i suspect it is an internal process of some sort. I basically need to give the auditors a valid reason. I haven't found anything on the net as of yet. 
    I ran a sql trace as well and it doesn't pickup the sa account being used with the audit login or audit logout fields.
    Do any of you guys know perhaps?

    **UPDATE**
    SELECT *
    FROM sys.dm_exec_sessions
    The above results show that the session id from 1 - 40 using the sa account login.
    The cmd's being used for session_id 1 - 40 are The cmd's being used for session_id 1 - 40 are 

    LOG WRITER, RECOVERY WRITER, LAZY WRITER, LOCK MONITOR, SIGNAL HANDLER, RESOURCE MONITOR, ect 


    These are sql internal commands which the sa uses and seems to be the reason why the login_time filed updates regularly, when these commands are triggered ...
    If anyone has something to add please shoot. :discuss:

    MCITP: Database Administrator 2005
    MCTS SQL Server 2008
    MCP SQL 2012/2014
    MCSA SQL Server 2012/2014
    MCSE Data Management and Analytics

  • February 7, 2018 at 6:15 am

    #1978847

    You want to filter for is_user_process = 1, as all the system processes appear as 'sa'

    And querying sys.dm_exec_sessions isn't sufficient for auditing. Create an extended events session or use SQLAudit.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 7, 2018 at 6:23 am

    #1978849

    Thanks Gail, will do 🙂

    MCITP: Database Administrator 2005
    MCTS SQL Server 2008
    MCP SQL 2012/2014
    MCSA SQL Server 2012/2014
    MCSE Data Management and Analytics

  • February 7, 2018 at 7:26 am

    #1978880

    Feeg - Wednesday, February 7, 2018 6:23 AM

    Thanks Gail, will do 🙂

    If you follow the normal recommendation of disabling the SA login, it should be rather a moot point to the auditors.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply