Last Password Change Time for SQL Login Account


Yesterday at work, I received an alert from Operations Manager (SCOM) that the password for the SQL Login account in one of instance is expired and need to be changed. While I checked the SQL Server Error Log for that instance, I found that there was only one entry of the above message. Although the password might be changed by the Application Owner, but I want to confirm the same, when the password was changed (the exact time).

Later last night, after coming back from office, I run a small test in one of my test machine. I created a SQL Login account (TestLogin) and then later changed the password.

-- Create a new SQL Login
USE [master];
GO
CREATE LOGIN [TestLogin] WITH PASSWORD = 'Test@1234';
GO
-- Verify the SQL Login Details
USE [master];
GO
SELECT * FROM sys.Server_Principals
where name = 'TestLogin';
GO

Once I verified the login account was created successfully, and then the next task was to change the password for the login account. I changed the password for the login account TestLogin and then check if I can track it when it was changed.

-- Change the password for the login TestLogin
USE [master];
GO
ALTER LOGIN [TestLogin] WITH PASSWORD = 'Test@3456@pass';
GO
-- Verify whether change is recorded
USE [master];
GO
SELECT [name], [type_desc], [create_date], [modify_date]
FROM sys.SERVER_PRINCIPALS
WHERE [name] = 'TestLogin';
GO

If you check the output of the columns Create_date and Modify_date, you will come to know, when the password was changed; however the Modify_date column records the time of the last change in the login account. For example, the below query will change the default_database setting for the login account TestLogin.

-- Change the default database to Firstweb
USE [master];
GO
ALTER LOGIN [TestLogin] WITH DEFAULT_DATABASE = [Firstweb];
GO
-- Verify the timestamp for the modify_date column
USE [master];
GO
SELECT [name], [type_desc], [create_date], [modify_date]
FROM sys.SERVER_PRINCIPALS
WHERE [name] = 'TestLogin';
GO

You might have noticed that the timestamp detail for the column modify_date is updated. As I didn’t find any solution for the above query, I posted my query on the SQLServerFAQ community page. When I checked this morning, I already had an answer for my query:

-- Last password changed time for the login TestLogin
USE [master];
GO
SELECT LOGINPROPERTY ('TestLogin', 'PasswordLastSetTime');
GO

The above query gives the timestamp, the last time the password for a login account was changed. You can read more about it here. Thanks to Amit and Prabhakar for their quick response, now need to read more about LoginProperty. In case you are in Facebook, you may want to check the group SQLServerFAQ.

Happy Learning 🙂

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in How To, Just Learned, Security in SQL Server, SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s