SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sa account login_time field


sa account login_time field

Author
Message
Feeg
Feeg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2155 Visits: 2048
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 DBA 2008
MCSA DBA 2014
GilaMonster
GilaMonster
SSC Guru
SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)SSC Guru (902K reputation)

Group: General Forum Members
Points: 902794 Visits: 48740
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


Feeg
Feeg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2155 Visits: 2048
Thanks Gail, will do Smile

MCITP: Database Administrator 2005
MCTS DBA 2008
MCSA DBA 2014
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)SSC Guru (871K reputation)

Group: General Forum Members
Points: 871114 Visits: 47454
Feeg - Wednesday, February 7, 2018 6:23 AM
Thanks Gail, will do Smile


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search