Post reply

Audit who is logging in as sa

  • February 28, 2014 at 5:02 am

    #304856

    Hi. Can anybody tell me how to audit a specific login instance-wide using Audit within SSMS? I've set up my audit, but my server audit specification is too broad. I can see that there is an Audit Action Type of SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP etc. but I can't seem to apply any filters that allow me to select just sa.

    Ideally I would like to capture the hostname, time logged on, and if possible certain actions that are performed (e.g. resetting a password, dropping a table); is this possible?

    I know I could use a server-side trace for this, but I understood that since Audit is built on Extended Events (?) it could be more lightweight than a trace if configured correctly.

    I definitely don't want to use triggers for this.

    I'm running Developer and Enterprise edition, patched to CU7.

    Thanks

  • February 28, 2014 at 6:17 am

    #1693019

    Do you need just the sa login or any successful login that had sysadmin at that time?

  • February 28, 2014 at 6:50 am

    #1693034

    Hi. It just needs to be that one login. We suspect that certain people know the password and are using it instead of their own (more restricted) logins.

    Thanks.

  • February 28, 2014 at 6:55 am

    #1693040

    I would recommend the background trace which is very easy to filer for just sa

  • February 28, 2014 at 6:58 am

    #1693041

    Is that definitely better than Audit, then (for this purpose)?

  • February 28, 2014 at 7:07 am

    #1693052

    Trace will provide all of the login auditing you are looking for including change password. It is easy to configure by using the profiler to build the script.

    Auditing the changes made by sa can also be done through trace.

    From a security perspective, if you know the password has been compromised, then it needs to be changed. Yes, this can be difficult if the password has been embedded in applications or jobs, but it needs to done at some point in time. Auditing only tells you who caused the outage, not preventing it from happening.

  • February 28, 2014 at 7:14 am

    #1693061

    Thanks; I'll go back to doing a server-side trace then.

    I agree with you about the password, but my manager is trying the diplomatic approach first, i.e. finding out who is using sa, discussing it with them and changing the password :-D.

  • February 28, 2014 at 7:14 am

    #1693062

    (I swear I only click once; it must be a network issue at my end, sorry.)

  • February 28, 2014 at 7:43 am

    #1693074

    Lizzie (2/28/2014)

    Thanks; I'll go back to doing a server-side trace then.

    I agree with you about the password, but my manager is trying the diplomatic approach first, i.e. finding out who is using sa, discussing it with them and changing the password :-D.

    I'd actually recommend disabling the SA login. It's not needed and databases can still be owned by it. Everyone should have their own Windows Login and no one should be using any system/SQL Server login. When such logins are required, no one but the "keeper of the books" should know what the passwords are.

    --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)

  • February 28, 2014 at 7:55 am

    #1693082

    I'd love to do that and so would a couple of the other DBAs so I'm hopeful that this will become the policy soon. Thanks for the help, all.

  • February 28, 2014 at 7:55 am

    #1693081

    I'd love to do that and so would a couple of the other DBAs so I'm hopeful that this will become the policy soon. Thanks for the help, all.

Viewing 11 posts - 1 through 10 (of 10 total)

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