how to capture login 'sa' begin used ot not

Question

how to capture login 'sa' begin used ot not

anoop.mig29

SSCrazy

Points: 2224
More actions
December 8, 2020 at 2:25 pm

#3818214

Hi
As per my subject line i would like to how to capture whether login account 'sa' is begin used to connect to server .Ie is there any session where login sa is begin used ??? Please tell me how should i proceed ahead

thanks and regards
Anoop

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Extended events would be the way to go for the login_successful event type where the username is sa and the session is a user session.

The user session is important as you wouldn’t want to capture the system logging in to do system related tasks as sa as that would be a false positive capture.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

To add to Ant-Green's comment, you may want to capture what queries are being run as sa too. Knowing that sa logged in is one thing, but if it is a malicious user, they could screw up your database so having a log of everything they did is not a bad idea.

On top of capturing the account sa, you may want to capture all users in the sysadmin role. This may be more complex than your original requirement, but if Steve is a sysadmin and his password gets compromised, you may want to know what Steve's account was doing.

Also, a good practice is to disable the sa account if you don't need it. Renaming it is also not a horrible idea IF it needs to be enabled. Mind you, both of these things should only be done AFTER you confirm that nothing is using it OR you change anything that is using it (application connections, linked servers, etc) to use a more appropriate account. And, as always, make the changes on test and then ensure they work before making any changes on live.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

My recommendation would be to do what the industry best practice normally recommends and disable it. If the phone rings, you'll have your answer and, yes, you should answer the phone to tell the people that are using it about the severe security violation they've committed and how they need to do something else other than login as the "SA" user.

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