Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot execute a USE statement while an application role is active

Posted on 2004-03-29
5
Medium Priority
?
278 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 2000 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 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