Application Roles for Cross-Database Joins

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.

thanks

Jason Schaitel

Jason_Schaitel  (jason_schaitel@hotm
ail.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/techin.../2000/books.asp

>
> 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.
>

Jason could instead enable the 'db chaining' database option for only those
databases needed by the application rather than turning the cross-database
chaining server-wide.

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

This is true, assuming the objects are owned by 'dbo', because database
ownership determines the dbo user mapping.  In the case of non-dbo-owned
objects, the object owners in the different databases need to map to the
same login in order to maintain an unbroken ownership chain.

>   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.

To expand on this BOL excerpt, it's necessary to enable the guest user in
the non-application role databases so that users have a security context
after the application role is enabled.  However, no permissions need to be
granted to guest or public in Jason's situation because access is done only
through views and procs from application role database.

--
Hope this helps.

Dan Guzman
SQL Server MVP

I have tried to look in BOL and Google Groups for the how to enable the
cross database ownership chaining option at the database level and not
having much luck. Can you point me to it?

thanks

Jason

Jason_Schaitel  (jason_schaitel@hotm
ail.com)  writes:
> I have tried to look in BOL and Google Groups for the how to enable the
> cross database ownership chaining option at the database level and not
> having much luck. Can you point me to it?

exec sp_dboption yourdb, 'db chaining', true

This option is not in the original Books Online, as it was added in SP3.
But it is in the updated Books Online, see link below.

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

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