Application role and other databases

[Arne Henrikson]

Hi out there,
I have run into a problem using application role and stored procedures.
In one database, we can call it FirstDB, i have created an application role
that can This role can, amongst other things, execute stored procedures
which are used from the application. The app role works fine as long as the
sp:s uses tables in FirstDB
But, I try to execute sp:s which reads other databases tables the fun is
over. Permission denied....
The BooksOnline tells me to create a guest account in the databases the sp
uses. For obvious reasons I dont want to create the guest account.
Is there another way to get the application role to work in a multi-datebase
way ?

[Dan Guzman]

You need to create a the guest account in the referenced databases so
that the application role has a security context but you don't need to
grant any permissions on the referenced objects as long as the databases
involved have the same owner and your objects are owned by the same user
(e.g. 'dbo'). This provides an unbroken ownership chain so that users,
including the application role, don't need permissions on referenced
objects. Only execute permission on the stored procedure is required.

Although non-application role users can access databases with the guest
user, they cannot access objects directly unless they've been granted
permissions. Consequently, assuming you grant permissions only on
objects in the application role database and only to the application
role, no other users will have access.

There is a July 2002 SQL Server Magazine article on this
<http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25145>. You need to
be a subscriber, though. Also, check out
<http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272424>
regarding inter-database ownership chains.

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

"Arne Henrikson" <arne.he...@nexans.com> wrote in message
news:qzOv9.967$Hb.6...@news2.ulv.nextra.no...