March 8, 2003 at 2:16 pm
Is it possible to use T-SQL to mimic Windows NT security that enforces password expiration using triggers. Has anyone ever thought about it. How can it be done.
Thanks
March 8, 2003 at 5:06 pm
You'd have to query on xdate2 in sysxlogins, then just reset the password to a random value. Don't think you'd need a trigger, just a simple job. Throw in some code to notify you/user.
Andy
March 10, 2003 at 2:50 am
Thanks, what info is held in xdate2?
March 10, 2003 at 3:46 am
It is a date value that is updated when the password is changed. Where did you find this out Andy as I must have missed that thread and I know this has been asked often.
The only thing I don't like is the fact with just changing it you cannot say if needs to be changed or the user typing incorrectly. Also, how do you allow the user to reset the password. if you have to do then you kinda defeat the purpose.
I would suggest create a table with SID and password next expiration but not all apps will check there so you have to come up with a swift way to handle notification and reset in yuor application.
March 10, 2003 at 6:16 am
Well, to be honest - I queried sysxlogins, found a login that was junk, ran sp_password, looked to see what if anything changed. Can't guarantee that is the only way xdate2 gets changed.
Password reset would get handled same as in NT - once it expires, you call the help desk. Definitely a more full featured implementation would warn the user a few days out and help them through a password change (which would also probably involve monitoring the history so they don't set it to the same value).
I only use sql logins for applications, so never had a great need for it. I'd want to treat those like NT 'never expire' type accounts even if I did set up an expiration plan.
Was hinted at Pass last year that Yukon would address this. Just a hint, Im not under NDA and that's ALL I know!
Andy
March 10, 2003 at 7:46 am
The application is an access front end using SQL Server as backend. I intend to provide a GUI for the users to change the password once it expires. I probably may use net send to alert the user to the expiration of the password. The idea of having a separate table to handle the info sounds good to me inview of the fact that xdate2 may not change as a result of password changes alone.
Thanks for your help.
March 10, 2003 at 7:52 am
How do I know when a user just logs in as I need to know this and trap the event. I can then check for the password expiration.
Thanks
March 10, 2003 at 9:28 am
I have the SQL 2K System Tables Map html help file, and it does not have the definition for the sysxlogins. Does anyone know where a definition of this table can be found? I googled, and found references to it, but not details on what the rest of the fields mean, and if they are useful. Thanks for the pointer Andy.
Tim C.
//Will write code for food
Tim C //Will code for food
March 10, 2003 at 9:58 am
Look at syslogins in BOL. It's a view, so you can see how it decodes sysxlogins.
To trap the login, you'd have to run a profiler session I think, or force all users to go through a login proc that you'd enforce as a convention.
Andy
March 11, 2003 at 8:21 am
You can find information about the sysxlogins table here:
March 11, 2003 at 9:36 am
I believe any change to the login (new defaultdb, language, etc) will trigger a chance on xdate2. However, once the login is setup, it is unlikely that anything other than a password is changed. We actually look at this, map it back to a user, and then send them an email to change the pwd. A week later, we randomly change it.
Steve Jones
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply