Posted by Erland Sommarskog on 08/26/05 00:32

Jason_Schaitel (jason_schaitel@hotmail.com) writes:
> I have an application that segregates data into two different
> databases. Database A has stored procs that perform joins between
> tables in database A and database B. I am thinking that I have reached
> the limits of Application Roles, but correct me if I am wrong.
> My application creates a connection to database A as 'testuser' with
> read only access, then executes sp_setapprole to gain read write
> permissions. Even then the only way 'testuser' can get data out of the
> databases is via stored procs or views, no access to tables directly.
> Anyone know of a solution? Here is the error I get:
>
> Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line
> 38
> Server user 'testuser' is not a valid user in database 'DatabaseB'
>
> The system user is in fact in database A and B.

Books Online says:

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.

That is, once you have set the application role in A, you are someone
else, and your access outside A is limited.

The one way I can think of to sort this out - beside uniting the databases
into one - is to enable the server configuration parameter "Cross DB
Ownership Chaining". This option was added in SP3 is off by default.
If there no other databases from other applications on the server,
there is no problem to enable this option. However, on consolidated
server that hosts databases for unrelated applications, this is not
recommendable.

For cross DB chaining to work, the databases must also have the same
owner.





--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]