• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Cannot execute a USE statement while an application role is active

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?

//Jonas


0
jopose
Asked:
jopose
  • 2
1 Solution
 
adwisemanCommented:
can you past the code for the procedure ChangePassword .

I would speculate that in this procedure, you a trying to change the database your connected to.  Once you set approle, you can not change databases.

This expert os from books online

"
When an application role is activated, the permissions usually associated with the user's connection that activated the application role are ignored. The user's connection gains the permissions associated with the application role for the database in which the application role is defined. The user's connection can gain access to another database only through permissions granted to the guest user account in that database. Therefore, if the guest user account does not exist in a database, the connection cannot gain access to that database.

After an application role is activated with sp_setapprole, the role cannot be deactivated in the current database until the user disconnects from SQL Server.

To protect your application role password, encrypt the password using the ODBC Encrypt function and specify odbc as the encryption method.

The sp_setapprole stored procedure can be executed only by direct Transact-SQL statements; it cannot be executed within another stored procedure or from within a user-defined transaction.

"
0
 
joposeAuthor Commented:
Well, the code in my sp is verry simple.
I don't use any "USE" statement BUT sp_PassWord does. I have tested to run the following code in Query Analyzer and I get the same result:
XEC sp_SetAppRole <rolename>, <rolepwd>

EXECUTE sp_PassWord <oldpwd>, <newpwd>

But you got me thinking!
Could this have someting to do with the fact that there is no guest account in SQL2000?
How can I create a guest account?

0
 
adwisemanCommented:
There is a guest account in SQL2000.  Simply add the user guest to the role in the database desired.  Guest does not show up as a user on the server logins, but it can be used.  See northwind as an example.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now