Last passoword updated/changed date and time in Azure SQL Database for sql logins

Question

Hi Team,

as part of compliance we are planning to generate report about when was the last time password changed/updated on all Azure sql database sql logins. Because each a every Azure SQL databases we found around 150 logins.

Please provide any T-SQL

Answer 1

Azure SQL Database does not expose the password last changed timestamp for SQL Logins directly via T-SQL. This is because SQL Logins in Azure SQL Database are scoped at the individual database level, not at the server level (unlike in SQL Server or Azure SQL Managed Instance). Additionally, Azure SQL Database doesn't support querying sys.sql_logins or the LOGINPROPERTY function, which you would normally use on an on-prem SQL Server or Azure SQL Managed Instance.

Workarounds:

Option 1: Track Changes Going Forward Using Custom Audit Table

If you need to start tracking password changes going forward, you can:

• Use a custom table and procedure to update SQL login passwords.
• Log the change in that table.

-- Example: Custom logging table CREATE TABLE dbo.LoginPasswordChangeLog ( LoginName NVARCHAR(128), ChangedBy NVARCHAR(128), ChangeDate DATETIME DEFAULT GETUTCDATE() );

-- Use this pattern in your admin scripts when changing passwords ALTER LOGIN [myuser] WITH PASSWORD = 'NewStrongPassword!'; INSERT INTO dbo.LoginPasswordChangeLog (LoginName, ChangedBy) VALUES ('myuser', SYSTEM_USER);

Option 2: Use Azure AD Logins or Azure Monitor Logs

If you're using Azure Active Directory for login, you can audit changes via:

• Azure AD logs (for identity events like password changes).

Azure Monitor / Diagnostic Logs, which can track login and user events if configured.

Option 3: Use Extended Events / Auditing (Limited)

You can configure SQL Auditing in Azure SQL Database to track login changes, but:

It does not include password change events for SQL logins.

It's more useful for detecting failed logins or modifications to users.