Solved

Cannot execute a USE statement while an application role is active

Posted on 2004-03-29
5
267 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now