ASP form and an SP

[AshleyRickards]

I'm looking for a SP that can interact with an ASP form so users can change there SQL password after a certain period of time.

Any ideas? Thanks. Ashley L Rickards
SQL DBA

 

[bperry]

I notice no one has replied yet today to your post, so I'll take a shot at it just to get the discussion rolling.

Of course, no doubt you know about the SP called sp_password. A user can use this to change her password, and here's just the little example from BOL:

This example changes the password for the login Victoria from ok to coffee.

EXEC sp_password 'ok', 'coffee', 'Victoria'


So that SP could probably be called from ASP just like you would any other SP.

If you wanted to do something more extensive, you could embed the sp_password SP inside your own SP, that has some other code in it.

Maybe something like this, just for illustration:

[tt]
CREATE PROC MyPass (
@user char(10),
@oldpass char(10),
@newPass char(10
)
AS
BEGIN
If @newpass = @oldpass
-- perhaps this is an error
Return 1

If @newpass = @user
-- maybe this is another error
Return 2

exec (sp_password @oldpass, @newpass, @user)
END

I believe that is the syntax for exec... not sure about that, since I never use it.
------------------------
[/tt]
Anyway, you get the idea. Something along those lines.


By the way, when you mentioned "their SQL password", I hope you were talking about their password under SQL Server authentication. If in fact your server is using Windows NT authentication, I don't think this method will work for changing passwords. (That's because under Win Auth, SQL Server does not control the password and therefore cannot change it.)

Hope that helps to get the ball rolling a bit.
rgrds, etc
bperry

 

[AshleyRickards]

thanks bperry!,

That gets things rolling ... now I need to figure a way to time it every 60 days or so to prompt user to cjange password. As you mentioned above I am indeed referencing SQL authentication. Thanks. Ashley L Rickards
SQL DBA

 

[dcrussel]

Perhaps one method to maintain the 60 day quota, would be to have a small table that contains

create table PwdLog
(
UserID char(10),
LastChange datetime
)

When a user attempts to login,
Check GETDATE and compare it to the date in the PwdLog table.
hint - use
if DATEDIFF(dd,LastChange,GETDATE()) > 60 (untested)
then run your SP.

Hope this helps,
dc