Application Roles for Cross-Database Joins

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason_Schaitel - 25 Aug 2005 22:07 GMT

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

Reply to this Message

Erland Sommarskog - 25 Aug 2005 22:32 GMT

> I have an application that segregates data into two different
> databases. Database A has stored procs that perform joins between
[quoted text clipped - 11 lines]
>
> 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.

Signature

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

Reply to this Message

Dan Guzman - 26 Aug 2005 13:49 GMT

> 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
[quoted text clipped - 3 lines]
> 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.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

Reply to this Message

Jason_Schaitel - 30 Aug 2005 21:19 GMT

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

Reply to this Message

Erland Sommarskog - 30 Aug 2005 22:11 GMT

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

Signature

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

Reply to this Message