SQL SERVER – Who Changed the Password of SQL Login? – Interview Question of the Week #065

Today’s interview question was not really asked interview, but it is indeed a real world scenario which everyone should know the answer of. I strongly suggest you bookmark this blog post at once in your lifetime you will need this blog post to know who changed the password of your SQL Login?

Question: Who changed the password of SQL Login?

Answer: Interacting with customers in my consulting role keeps me up-to-date with current versions, but sometimes I get opportunity to brush up my basics also. This blog is the result of one such interaction. Never are questions and requirements from customers easy. They come to us only when something is really wrong or they are not able to figure out after spending a couple of hours on the various search engines / forums. Even better when their internal IT team just turns their back. Some of these requirements come when the year is about to end and there is auditing happening inside the organization. I was fortunate when one of the internal audits inside a company got the better and they reached out to me before the external auditor can question them.

My client noticed that on a random basis, SQL “SA” account password was getting reset and they wanted to know who is doing it? I knew that SQL Server error log does not record login password change events. So my next first thought was “Default trace” should have that data. On my lab machine I ran below query to find out the events captured

SELECT @Trace_ID = id
FROM sys.traces
WHERE is_default = 1;
	,e.NAME AS Event_Description
FROM sys.fn_trace_geteventinfo(@Trace_ID) t
INNER JOIN sys.trace_events e ON t.eventID = e.trace_event_id
WHERE e.name like '%Audit%'

EventID Event_Description
—– ——————-
18 Audit Server Starts And Stops
20 Audit Login Failed
102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event
115 Audit Backup/Restore Event
116 Audit DBCC Event
117 Audit Change Audit Event
152 Audit Change Database Owner
153 Audit Schema Object Take Ownership Event
175 Audit Server Alter Trace Event

Unfortunately, the SQL Server default trace records only Audit Login Change Property Event Class and not Audit Login Change Password Event Class. So the client may need to run custom trace to track password changes.

So, we are left with no choice but monitor it via a new trace. Another thought which came to my mind was that can we make use of fn_dblog to read the transaction log. I told my client that this method is completed undocumented. Another problem with this approach is that if the checkpoint is fired the data would be flushed in T-log because master database is in simple recovery model. But this was a great start for them to at least debug what was happening in the system.

Here is what I provided them as script to troubleshoot:

Create the Login for demo

USE [master]
CREATE LOGIN [SQLLogin1] WITH PASSWORD=N'@Very$trongP@ssw0rd123', DEFAULT_DATABASE=[master], 

Use this to Change Password

USE [master]
ALTER LOGIN [SQLLogin1] WITH PASSWORD=N'VerystrongP@ssword123'

Query to find who changed the password

SELECT	[Transaction SID]
	,suser_sname([Transaction SID]) AS 'Login Name'
WHERE [Transaction Name] = 'ALTER LOGIN'

SQL SERVER - Who Changed the Password of SQL Login? - Interview Question of the Week #065 who-01

Query to find which account password got changed

SELECT [Lock Information]

SQL SERVER - Who Changed the Password of SQL Login? - Interview Question of the Week #065 who-02

Now we can use below to find which account is 270 (as highlighted above)

SELECT name  
FROM sys.server_principals
WHERE principal_id = 270

SQL SERVER - Who Changed the Password of SQL Login? - Interview Question of the Week #065 who-03

Have you ever used such undocumented command? Use this with care as this is not a supported command. So please make sure you are careful in it’s usage. I am sure there are tons of interesting ways in which you might use this blog, I am very much interested in knowing some of them. Do let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, SQL Password, SQL Scripts, SQL Server, SQL Server Security
Previous Post
What is a Self Join? Explain with Example – Interview Question of the Week #064
Next Post
What is ACID Property in Database? – Interview Question of the Week #066

Related Posts

11 Comments. Leave new

  • Hi, Just wanted to say THANK YOU for all of your posts over the years.

  • Mohd Amir Ansari
    April 6, 2016 4:23 pm

    Very informative! Quite useful!

  • John Langston
    April 6, 2016 6:34 pm

    Questions such this are for me a great form of a self-test, helping me stay sharp. Thanks!

  • Hi Pinal, thank you for this very interesting posting. I did give the scripts a try but came up a different result in the query of finding out who changed the password. The Login Name is Null instead of my name. I was using Windows Authentication login. Would that be the cause of the problem?

    Thanks again!

  • Michelle Poolet
    April 7, 2016 3:42 am

    how about the sp_MSforeachdb undocumented procedure? This one is sooooo useful!

  • hello sir , im having a problem while installing a sql native client10, the system cannnot ope the device or file specified

  • I ran this query SELECT [Transaction SID]
    ,suser_sname([Transaction SID]) AS ‘Login Name’
    FROM::fn_dblog(DEFAULT, DEFAULT)
    WHERE [Transaction Name] = ‘ALTER LOGIN’

    on a test server and it is running for over 6 minutes. Any idea on why?

  • Siniša Marinić
    May 3, 2019 2:45 pm

    Hi, I find this usefull for ALTER LOGIN, but in my case I need something like the Title: “Who Changed the Password of SQL Login”. If I change default database for that login it will be a resault of that ALTER LOGIN change.

    Is there a way to get ALTER LOGIN explicitly for changing password?

  • Thanks Pinal. Very Helpful post..


Leave a Reply