Re: Application Role & 2nd Database

From: Jeff Price (price.9_at_osu.edu)
Date: 02/13/04


Date: Fri, 13 Feb 2004 09:38:18 -0500

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



Relevant Pages

  • Re: Public and Guest
    ... the guest account exists in all databases but is ... enabled by default only in system databases. ... allowed until you explicitly added the user to the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Guest account in SQL 2005?
    ... The guest account in SQL Server serves the same basic function as the ... just like you wouldn't grant permissions to the Everyone group in Windows. ... priviledges in the database. ...
    (microsoft.public.sqlserver.security)
  • Disabling guest account doesnt work as expected (please help!)
    ... Remove guest user from the database you want to revoke ... I want to disable access of the SQL guest account ... >disabling the account, but why does it still show up in ...
    (microsoft.public.sqlserver.server)
  • Re: Is there a way to Use an Application Role with more than one database?
    ... So you are still getting the 'is not a valid user in database' error? ... Did you enable the guest user? ... permissions to systems objects in the ProductionDB?) ... EXEC sp_dboption 'DevelopmentDB', 'db chaining', true ...
    (microsoft.public.sqlserver.security)
  • Re: Guest account in SQL 2005?
    ... You should not need go grant permissions to the guest user as that defeats ... Did you set DB_CHAINING ON for both database A and B? ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)