is anything using the sa account?

Question

is anything using the sa account?

snomadj

SSCarpal Tunnel

Points: 4491
More actions
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?

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

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)

snomadj SSCarpal Tunnel Points: 4491 More actions · Answer 6

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.

SQLBill SSC Guru Points: 51440 More actions · Answer 7

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

Lee Linares SSCrazy Points: 2727 More actions · Answer 8

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;