Post reply

is anything using the sa account?

  • November 2, 2016 at 4:23 am

    #322531

    I'm reviewing an estate i've just taken over. I have some servers where the sa account is not disabled. I want to disable it. Other than disabling it and seeing what breaks, how do I determine definitively if it is ever used?

    I have a trace running but realistically I need this to run for a month to be sure there are no monthly app jobs & I don't want to wait.

    Is there any other way?

  • November 2, 2016 at 4:50 am

    #1910056

    You could use one of any number of auditing techniques instead - a logon trigger for example. But you'll still need to run it for a month (or whatever other period you deem necessary) to be sure that the account isn't being used.

    John

  • November 2, 2016 at 8:01 am

    #1910101

    snomadj (11/2/2016)

    I'm reviewing an estate i've just taken over. I have some servers where the sa account is not disabled. I want to disable it. Other than disabling it and seeing what breaks, how do I determine definitively if it is ever used?

    I have a trace running but realistically I need this to run for a month to be sure there are no monthly app jobs & [font="Arial Black"]I don't want to wait.

    Is there any other way?[/font]

    I could be wrong but I don't believe so. To the best of my knowledge, SQL Server doesn't even have something as easy as a "last login date". IIRC, Active Directory does but that won't help with SQL Server Logins such as "SA".

    --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)
    Intro to Tally Tables and Functions

  • November 2, 2016 at 10:53 am

    #1910178

    You could run a lightweight server-side trace on just that login for a period of time. Test it outside Production first.

  • November 2, 2016 at 12:23 pm

    #1910203

    There is a lot to be said for simply disabling the 'SA' account just to see if someone outside the DBA team is crass enough to actually complain.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • November 2, 2016 at 12:47 pm

    #1910208

    Eric M Russell (11/2/2016)

    There is a lot to be said for simply disabling the 'SA' account just to see if someone outside the DBA team is crass enough to actually complain.

    Considering how serious a problem having outside "SA" logins is, I'd do the same. I'd get some buy-in from management, though, so that you don't get an escort to the back door.

    --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)
    Intro to Tally Tables and Functions

  • November 2, 2016 at 4:46 pm

    #1910251

    Cheers all, nice to know I'm not missing a trick. Last login / host / prog would be VERY useful.

    I've set up a job to check logins every minute for a week. I'll email app owners & management then disable & monitor for failed login attempts thereafter.

  • November 2, 2016 at 5:10 pm

    #1910253

    There is a default trace that might help depending on when you last rebooted the server.

    DECLARE @tracepath VARCHAR(1000)

    SELECT @tracepath = [PATH] FROM sys.traces WHERE is_default =1

    SELECT @starttime, endtime, textdate, databasename, ntusername, hostname, applicationname, loginname, sessionloginname

    FROM ::fn_trace_gettable(@TracePath, DEFAULT)

    ORDER BY starttime ASC;

    -SQLBill

  • November 3, 2016 at 9:24 am

    #1910391

    You might want to filter on just capturing rows with 'sa' as the login name. Also, if you want to query ALL the default trace files you need to remove the rollover number:

    DECLARE

    @TracePath NVARCHAR(1000)

    ----------------------------------------------------

    ---- Parse the file name without rollover number

    ----------------------------------------------------

    SELECT @TracePath = REVERSE(SUBSTRING(REVERSE([path]), CHARINDEX('\', REVERSE([path])), 256)) +

    LEFT(RIGHT([path],CHARINDEX('\',REVERSE([path]),1)-1),CHARINDEX('_',(RIGHT([path],CHARINDEX('\',REVERSE([path]),1)-1)),1)-1) + '.trc'

    FROM sys.traces WHERE is_default = 1;

    SELECT starttime, endtime, textdata, databasename, ntusername, hostname, applicationname, loginname, sessionloginname

    FROM fn_trace_gettable(@TracePath, DEFAULT)

    WHERE loginname='sa'

    ORDER BY starttime ASC;

Viewing 9 posts - 1 through 8 (of 8 total)

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