Re: Guest account in SQL 2005?
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 29 Dec 2005 13:13:57 -0600
> 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?
>
.
- Prev by Date: Re: Got hacked and now cannot Generate SSPI context
- Next by Date: Re: Cannot login SQL Server with 'sa'
- Previous by thread: Got hacked and now cannot Generate SSPI context
- Next by thread: Re: sql browser registry access
- Index(es):
Relevant Pages
|
|
