The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Monday, October 7, 2024 Login
Public

Blog posts for the month of September,2011.
SQL Server Last date / time a password was changed9/13/2011 1:40:27 PM

I don't know why, but this simple request for SOX Compliance took me a while to find. There is a function that retrieves login properties, LOGINPROPERTY, duh! Of interest is the PasswordLastSetTime.

It also has many other useful properties, see: http://msdn.microsoft.com/en-us/library/ms345412(v=SQL.100).aspx . It now becomes very easy to drop into any query, below is one of many sox queries we run that is now required to include this:


SELECT --l.sid, loginname AS [Login Name], loginproperty(loginname,'PasswordLastSetTime') as PasswordLastSetTime, dbname AS [Default Database], CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login' END AS [Login_Type], sl.is_disabled, sl.is_policy_checked, sl.is_expiration_checked, CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User' ELSE '' END AS [AD Login Type], CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin], CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin], CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin], CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin], CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin], CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin], CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator], CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin] FROM master.dbo.syslogins l left outer join sys.sql_logins sl on l.sid = sl.sid ORDER BY[Login Name], [Login_Type], [AD Login Type]

Blog Home