We have a VB6 applicatoin that has been running on a SQL 7 server. Now we have Installed a new server with Win2003 and SQL 2000.
The applikation uses a stored procedure to change the users passwords (ChangePassword).
This sp uses the system sp sp_PassWord.
Execute is granted to an application role and not to the users.
So in order to execute the sp the following code is used:
EXEC sp_SetAppRole <rolename>, <rolepwd>
EXECUTE ChangePassword <oldpwd>, <newpwd>, <uid>
We then get the following error:
"Cannot execute a USE statement while an application role is active"
This worked fine in SQL7 but not in SQL2000.
The reason for using another sp than sp_PassWord is that I have som extra code in my sp.
A work-aound would of course be that i grant execute to public and skipped sp_SetAppRole but I don't want to do that. Is ther another way?