How to Check when an SQL Account was Last Modified

Paopatsin/ January 30, 2021/ Database, MS SQL, Technology

Please read our Disclaimer

Works With
-Microsoft SQL Server 2016 (RTM) – 13.0.1601.5 (X64)
-Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64)

One day, some SQL agent jobs were no longer running, so Pao went to check the issue, he fired up his SQL Management Studio and went to check for the ailing jobs. Upon checking the error logs, he found out that the service account password was no longer valid. So Pao went further to check when the account was last modified.

Command:

USE master GO SELECT * FROM sys.sql_logins WHERE [name] = ‘[replace with account to check]’ GO

Steps and Sample Usage:

1. Open and log into your SQL Management Studio
2. Open a New Query Windows or (CTRL+N)
3. Run the following commands: (Copy and Paste the code block to your Query Window)

–Let’s check details for user ‘sa’
USE master
GO
SELECT *
FROM sys.sql_logins
WHERE [name] = ‘sa’
GO

4. Execute the query by pressing F5.

Use Case:

1. Use to check details of sudden account changes, for investigation purposes.

Caveat:

1. The Modified Date (modify_date) is not limited to just password change, it pertains to any modifications made to the account.