Solved

Cannot execute a USE statement while an application role is active

Posted on 2004-03-29
5
268 Views
Last Modified: 2008-03-10
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
Comment
Question by:jopose
  • 2
5 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 10704126
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
 

Author Comment

by:jopose
ID: 10704333
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
 
LVL 14

Accepted Solution

by:
adwiseman earned 500 total points
ID: 10704392
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question