How to implement Application Roles for an application?

I want to know how to implement Application Roles for an application which
uses mutliple databases. Based on what I have read, Application Roles are
tied to one database only and accessing other databases requires adding gues
t
user account to other databases. For example, if an application is using on
e
database to contain access rights data to its functions and another database
for actual transaction data, how can I implement Application Role?
Thanks.Your understanding is correct; once an application role is activated, other
databases can be accessed only via the guest user security context.
If you don't want to grant permissions to guest or public in the other
databases, consider creating referencing views or procs in your application
role database and enabling cross-database chaining in the databases
involved. As long as the objects involved have the same owner, permissions
are not needed on indirectly referenced objects. Note that the databases
also need to be owned by the same login in order to maintain an unbroken
ownership chain for dbo-owned objects.
You should enable cross-database chaining only if you fully understand the
security implications. See the Books Online
<instsql.chm::/in_runsetup_1cj5.htm> for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:36AC8304-5158-4335-B1C4-B503F51E3F43@.microsoft.com...
>I want to know how to implement Application Roles for an application which
> uses mutliple databases. Based on what I have read, Application Roles are
> tied to one database only and accessing other databases requires adding
> guest
> user account to other databases. For example, if an application is using
> one
> database to contain access rights data to its functions and another
> database
> for actual transaction data, how can I implement Application Role?
>
> Thanks.|||Hi Dan,
Can you elaborate little bit more about referencing views and procedure?
My understanding of Application role:
1. It is connection-based (session-based).
2. It needs to be activated by using sp_setapprole.
3. By default, application role has no permission on the databases. So,
permissions need to be assigned to the role.
I'm trying to develop an application which can connect to SQL Server using
either SQL Authentication or Windows Authentication through ODBC. The
administrator(s) of the application can configure whether the databases
created by the application can be accessed by other applications (this is wh
y
I'm looking at application role). The application is using multiple
databases per connection/session.
Thanks,
Peter
"Dan Guzman" wrote:

> Your understanding is correct; once an application role is activated, othe
r
> databases can be accessed only via the guest user security context.
> If you don't want to grant permissions to guest or public in the other
> databases, consider creating referencing views or procs in your applicatio
n
> role database and enabling cross-database chaining in the databases
> involved. As long as the objects involved have the same owner, permission
s
> are not needed on indirectly referenced objects. Note that the databases
> also need to be owned by the same login in order to maintain an unbroken
> ownership chain for dbo-owned objects.
> You should enable cross-database chaining only if you fully understand the
> security implications. See the Books Online
> <instsql.chm::/in_runsetup_1cj5.htm> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:36AC8304-5158-4335-B1C4-B503F51E3F43@.microsoft.com...
>
>|||> Can you elaborate little bit more about referencing views and procedure?
You can create views and/or procs in your application role database that
reference those objects in other databases needed by your application. For
example:
USE MyAppRoleDatabase
GO
CREATE VIEW dbo.MyView
AS
SELECT SomeData
FROM MyOtherDatabase.dbo.MyTable
GO
GRANT ALL ON dbo.MyView TO MyAppRole
GO
As long as MyAppRoleDatabase and MyOtherDatabase are owned by the same
login, the ownership chain for dbo-owned objects is unbroken. This allows
the application role to access MyTable data via MyView even without
permissions the underlying table. As mentioned earlier in this thread, the
guest user needs to be enabled in MyOtherDatabase in order to establish a
security context for the application role.

> 3. By default, application role has no permission on the databases. So,
> permissions need to be assigned to the role.
This is true but note that permissions are needed only on the objects
*directly* referenced by the role. Permissions are not checked on
indirectly referenced objects as long as the ownership chain is unbroken.

> I'm trying to develop an application which can connect to SQL Server using
> either SQL Authentication or Windows Authentication through ODBC.
Application roles can be used with either authentication method.

> The administrator(s) of the application can configure whether the
> databases
> created by the application can be accessed by other applications (this is
> why
> I'm looking at application role).
Are you saying that you have multiple applications and application roles?
In this case, each application role database would need referencing objects
as described above.
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:6362673A-F318-429B-946A-FC66BFA2D490@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> Can you elaborate little bit more about referencing views and procedure?
> My understanding of Application role:
> 1. It is connection-based (session-based).
> 2. It needs to be activated by using sp_setapprole.
> 3. By default, application role has no permission on the databases. So,
> permissions need to be assigned to the role.
> I'm trying to develop an application which can connect to SQL Server using
> either SQL Authentication or Windows Authentication through ODBC. The
> administrator(s) of the application can configure whether the databases
> created by the application can be accessed by other applications (this is
> why
> I'm looking at application role). The application is using multiple
> databases per connection/session.
>
> Thanks,
> Peter
>
> "Dan Guzman" wrote:
>