Solved

Cannot execute a USE statement while an application role is active

Posted on 2004-03-29
5
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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