Re: Application Role and access to 'other' databases

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 05/19/03 

Date: Sun, 18 May 2003 20:20:28 -0500

Once an application role is activated, other databases can be accessed
only via the guest user security context. This is described on the
Books Online (adminsql.chm::/ad_security_89ir.htm).

One method for an application role to access objects in other databases
is to employ cross-database chaining. This will work only if the
objects involved have the same owner (owners map to same login). If
your objects are owned by 'dbo', the databases need to be owned by the
same login.

You can use cross-database chaining with application roles as follows:

1) create views and procedures in your primary database that reference
objects in the second database

2) grant permissions to the app role

3) add the guest user to the second database (no permissions required)

4) enable cross-database chaining for both the primary and secondary
databases

5) have your application access other database via the referencing
objects in the primary database. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Bottomless Pit" <pauldurdin@iee.org> wrote in message
news:%23jSB4oZHDHA.2176@TK2MSFTNGP10.phx.gbl...
> I am using an application role to access the primary database used by
my
> application. This works really well and has allowed me to use
integrated
> security and give users access to the database through views, but have
full
> access the my program.
>
> I need to read data from another database on the same server, which is
ok
> normally by specifying
>
> SELECT * FROM OtherDatabase.owner.tablename
>
> However if you use an application role the integrated login account no
> longer has access to the 'other database'.
>
> I Oracle I can create a query across databases by specifying the
account
> details for the second database, and I would like to do the same in
SQL
> Server.
>
> The other database I can connect to as a SQL Server account, so
something
> like;
>
> SELECT * FROM
> OtherDatabase.owner.tablename,User=UserName,Password=UserPassword
>
> I have tried both linked servers and remote databases, but neither
seem to
> work on the same machine. It could be that I don't really understand
how it
> set up a linked server ok course!
>
> Any ideas anyone?
>
> regards,
>
> Paul Durdin
>
>
>

Relevant Pages
Loading