Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm porting stored procedures from Sybase 15 to SQL Server 2008.

In Sybase this statement calcs an expiration date by adding a number of days to syslogins.pwdate (Date the password was last changed) and works fine:

SELECT @l_pwd_date = dateadd( day, @l_pwd_max_expire, pwdate)
FROM master.dbo.syslogins
WHERE name = @v_user

...but throws this error when trying to compile it in SQL Server 2008:

Msg 207, Level 16, State 1, Line 21 Invalid column name 'pwdate'.

syslogins in SQL Server 2008 no longer includes that column pwdate.

Does anyone know where could I find equivalent column in SQL Server 2008 or a workaround to find the date a login password was last changed?

share|improve this question

migrated from stackoverflow.com Dec 2 '11 at 21:35

This question came from our site for professional and enthusiast programmers.

    
FYI, syslogins is deprecated to sys.server_principals whic still doesn't have it. Nor sys.sql_logins –  gbn Dec 2 '11 at 21:41

1 Answer 1

up vote 11 down vote accepted

Use the PasswordLastSetTime option of the LOGINPROPERTY.

SELECT LOGINPROPERTY('YourLoginName', 'PasswordLastSetTime');
share|improve this answer
    
Yay, Joe is on DBA now! –  JNK Dec 2 '11 at 21:40
    
@JNK: Thanks for the warm welcome. :-) –  Joe Stefanelli Dec 2 '11 at 21:42
    
Sweet. Didn't know about that function. Welcome to data professionals to (trying to change the name :-). And pop-in to say hello: chat.stackexchange.com/rooms/179/the-heap –  gbn Dec 2 '11 at 21:43
1  
Appreciate the time Joe, thanks; it worked fine. Heads up for those using windows integrated authentication, it will return NULL because login_name must be the name of a SQL Server login. –  Only You Dec 5 '11 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.