Re: Guest account in SQL 2005?
- From: "Begie" <Begie@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 4 Jan 2006 03:37:02 -0800
Sorry, too quickly with previous mail.
I added connect privilges, and the got another error. Went in the log file
and noiced i got an error stating "no select privileges.....'
so i also added select permissions on the guest user, and now it works..
I don't get the logic behind the guest user, but apparantly if the user you
are using is not in the database B, sql uses this guest account, and the
privileges given to this guest accout. as this is a one way replicated db,
its no problems to give peopel access to this database, i suppose if the
guest account has no alter rights, they can't change anything...
Thnx for the help..
"Dan Guzman" wrote:
> > When i want to see the view, i get following error: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The server principal "TESTUSER" is not able to
> > access the database "DATABASE B" under the current security context.
>
> Did you grant CONNECT to guest in database B? You will get the security
> context error if you haven't done that.
>
> > I have the same setup in SQL 2000, and if i remembered correctly, i only
> > had
> > to add the user guest in database B with sql2000, and give this user the
> > corect rights.
>
> Granting permissions to the guest user has the side-effect of granting all
> server accounts access to the data. Assuming all objects involved have the
> same owner, you can instead grant no permissions to guest and enable cross
> database chaining in both databases so that database A users can access
> database B data only through your database A view. The 'db chaining'
> database option must be turned on in both databases in order for the
> cross-database ownership chains to be honored.
>
> SQL 2000: EXEC sp_dboption 'MyDatabase', 'db chaining', true
>
> SQL 2005: ALTER DATABASE MyDatabase SET DB_CHAINING ON
>
> For dbo-owned objects, both databases need to have the same owner so that
> the ownership chain is unbroken for dbo schema objects. Execute
> sp_changedbowner, if needed.
>
> See the Books Online for more information
> <ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/762249ee-881a-4c3e-b8c0-3a9475039aca.htm>.
>
> --
> Happy Holidays
>
> Dan Guzman
> SQL Server MVP
>
> "Begie" <Begie@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:3698F0FF-69A1-42C7-BE44-649B49D13DEE@xxxxxxxxxxxxxxxx
> >I have the following situation with SQL 2005:
> > Database A has a view that gets some data from a table in Database B.
> > SQLUser has rights to database A
> > User has no rights to databasae B
> >
> > When i want to see the view, i get following error: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]The server principal "TESTUSER" is not able to
> > access the database "DATABASE B" under the current security context.
> >
> > I have the same setup in SQL 2000, and if i remembered correctly, i only
> > had
> > to add the user guest in database B with sql2000, and give this user the
> > corect rights.
> >
> > When i put the user as DBOwner in database A, everything works, but i
> > don't
> > want to make every user dbowner.
> >
> > Has anyone come acros this?
> > Is there a way to specify with what credentials a view goes to another
> > database on the same server?
> >
>
>
>
.
- Follow-Ups:
- Re: Guest account in SQL 2005?
- From: Michael Hotek
- Re: Guest account in SQL 2005?
- From: Dan Guzman
- Re: Guest account in SQL 2005?
- Prev by Date: Re: Guest account in SQL 2005?
- Next by Date: Re: Cannot log into a SQL Server 2005 database
- Previous by thread: Re: Guest account in SQL 2005?
- Next by thread: Re: Guest account in SQL 2005?
- Index(es):
Relevant Pages
|
|
