Application Role & 2nd Database

I'm trying to use application roles for the 1st time and have a problem.
Most of the select statements I use reference tables in a separate database.
After reading the BOL I find that this only works through the GUEST account.
Drat!
Any suggestions to fix this? I'm considering setting up views in the local
DB, but this will involve changing the code and creating the views and I
still don't know if it will work.
--
Jeffrey R. Price
Database Manager
Computing & Communication Services
Max M. Fisher college of Business
The Ohio State University
320F Mason Hall
250 W. Woodruff Avenue
Columbus, OH 43210-1309Creating referencing views will work fine. If you don't want to grant
permissions on the underlying tables to guest or public, you can use an
unbroken cross-database ownership chain so that the app role needs only
permissions on views in the application role database.
To maintain an unbroken chain, the owners of the objects involved need to
map to the same login. If your objects are owned by 'dbo', this
necessitates that the owners of both databases be the same. Also, the
cross-database chaining option (introduced in SQL 2000 SP3) needs to be
enabled for those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Thanks, I can run it fine now from Query Analyzer, but not my app... more
work to do there.
I had to set the DB owners as the same account and a few other tasks. The
following MSDN article helped:
http://msdn.microsoft.com/library/d.../>
up_1cj5.asp
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ecqxs2D8DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Creating referencing views will work fine. If you don't want to grant
> permissions on the underlying tables to guest or public, you can use an
> unbroken cross-database ownership chain so that the app role needs only
> permissions on views in the application role database.
> To maintain an unbroken chain, the owners of the objects involved need to
> map to the same login. If your objects are owned by 'dbo', this
> necessitates that the owners of both databases be the same. Also, the
> cross-database chaining option (introduced in SQL 2000 SP3) needs to be
> enabled for those databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Correction......
I did not get the Application role to work across Databases. I continue to
receive the error "Server user 'price_9' is not a valid user in database
'FCoB_Contacts'."
I've tried both
EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
and
EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
Both DBs are owned by the same account.
Drat!
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Since an application role is only known in a single database, you need to
enable the 'guest' user in the other database so you have a security context
in the other database. No guest user permissions need to be granted. For
example:
Use MyOtherDatabase
EXEC sp_adduser 'guest'
Also, you don't need to enable cross-database chaining at the server level.
Your can specify it at the database level for only the databases that need
this option turned on:
EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Correction......
> I did not get the Application role to work across Databases. I continue
to
> receive the error "Server user 'price_9' is not a valid user in database
> 'FCoB_Contacts'."
> I've tried both
> EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
> and
> EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
> EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
> Both DBs are owned by the same account.
> Drat!
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Thanks! That did the trick.
Do we need to be concerned with the existence of the "Guest" account?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
> Since an application role is only known in a single database, you need to
> enable the 'guest' user in the other database so you have a security
context
> in the other database. No guest user permissions need to be granted. For
> example:
> Use MyOtherDatabase
> EXEC sp_adduser 'guest'
> Also, you don't need to enable cross-database chaining at the server
level.
> Your can specify it at the database level for only the databases that need
> this option turned on:
> EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
> EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> to
problem.
I
>|||> Do we need to be concerned with the existence of the "Guest" account?
As long as you haven't granted additional permissions to guest or public,
guest user access is limited to default public role permissions. This
includes the ability to view meta data, (e.g. table aned column names) but
no access to user objects and data. Users will still need an account to
connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:OkTtG8j8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Thanks! That did the trick.
> Do we need to be concerned with the existence of the "Guest" account?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
to
> context
For
> level.
need
continue
database
with
> problem.
the
and
> I
>