I have a stored procedure that is used by a user to change their password:

CREATE PROCEDURE "UserChangePassword" (IN "PUsername" VARCHAR COLLATE "UNI", IN "PPassword" VARCHAR COLLATE "UNI")
BEGIN      
  -- Note that USER requires double quotes while PASSWORD requires single quotes
  EXECUTE IMMEDIATE 'ALTER USER ' + QUOTEDSTR(PUsername, '"') + ' ' +
                    '  PASSWORD ' + QUOTEDSTR(PPassword, '''');

END

If I run the stored proc from EDB Manager it works fine, but if I run it within an application I get this error:

"ElevateDB Error #300 An error occurred with the statement at line 4 and column 22 (Cannot lock the user John in the configuration file DBConfig for exclusive access)."

Is this because the user changing their password is currently logged in?

= Steve

Steve


Have you set the SP to run in the contect of the Configuration database?

Roy Lambert

Steve,

<<Is this because the user changing their password is currently logged in?>>

Yes.
Stored procedures always use the "System" user within the context of the procedure execution, so if the procedure is called to change the current user, it will fail because the currently logged in user can't be locked.
To change the password of the current user you will have to directly execute an ALTER USER statement.

--
Fernando Dias
[Team Elevate]

<< Yes.
Stored procedures always use the "System" user within the context of the procedure execution, so if the procedure is called to change the current user, it will fail because the currently logged in user can't be locked.
To change the password of the current user you will have to directly execute an ALTER USER statement. >>

Thanks Fernando. I ran it as a query from the application and it works fine.

= Steve