sp_password from within a stored proc

Question

sp_password from within a stored proc

Simon-220850

SSCrazy

Points: 2789
More actions
July 17, 2006 at 9:31 am

#62551

Hi we have 3rd party app that has a stored proc that reads the contents of a table containing password changes (password_change) and executes them.
The procedure is as follows
CREATE PROCEDURE userpass_test
AS
/***************************************************************
* Test if there are any passwords awaiting change and execute *
***************************************************************/
/* DELARE TEMPORARY VARIABLES
*/
DECLARE @user char(32)
DECLARE @pass char(30)
/* DECLARE CURSOR TO ITERATE THROUGH RECORD
*/
DECLARE cur_userpass SCROLL CURSOR
FOR SELECT userid, newpass FROM password_change
FOR UPDATE
/* OPEN CURSOR
*/
OPEN cur_userpass
/* FETCH FIRST ROW INTO TEMPORARY VARIABLES
*/
FETCH FIRST FROM cur_userpass INTO @user, @pass
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
  EXEC sp_password @new=@pass, @loginame=@user
  SELECT @pass,@user
  DELETE FROM password_change WHERE userid = @user
END
FETCH NEXT FROM cur_userpass INTO @user, @pass
END
/* CLOSE CURSOR
*/
CLOSE cur_userpass
/* DEALLOCATE CURSOR FROM MEMORY (DESTRUCTOR)
*/
DEALLOCATE cur_userpass

The stored procedure reports "Password Changed" for each record, yet the password does not seem to change to the new value. I have confirmed
Has anyone any ideas on this ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

They are not checking the success/failure of sp_password before clearing out the password_change row. Sp_password has a number of validations that it runs before it changes the password. I would say that something is causing sp_password to fail, but since the calling SP is not set up to look at the success/failure of sp_password, it appears to be working correctly. I would suggest checking the return value of sp_password and then stepping through it to find where the problem is.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Simon-220850 SSCrazy Points: 2789 More actions · Answer 2

Hi John,

The message reported when executed in QA is "Password Changed", Also I changed the line

EXEC @returnvalue=sp_password @new=@pass, @loginame=@user

SELECT @returnvalue

and @returnvalue contains 0, but the password is still not changed

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Is this being executed by a sysadmin? I don't think it works unless a sysadmin runs it. An individual user can run it, but only for themself, not for any other user.

Simon-220850 SSCrazy Points: 2789 More actions · Answer 4

Hi Steve,

Yes pretty sure I was logged in as a sysadmin, I also ran sp_password in isolation outside the userpass_test SP in the same Query Analyzer session and it worked OK (ie user had to use their new password when logging in)

I was wondering whether it had anything to do with the owner of the user stored procedure userpass_test that is calling it. Will have to check when I'm back at my desk tomorrow

Cheers