Ownership Chains Not Working?

I have two databases, say A & B.
I have a table in database A called Table 1.
I have a view in database B called View 1.
B.View 1 selects * from A.Table 1.
Both the view and the table are owned by dbo.
I create a new user and grant him select on B.View 1 and database permission
on only database B.
When this user queries B.View 1, he gets an error saying he is not a user of
database A. Why?
I thought the chain of ownership for dbo for both table and view would allow
this scenario to work? I don't want this user to be a member of the public
role for database A, I only want him to be a member of database B.
Any help would be appreciated!
Thanks,
BenThe user needs to be a valid user in both databases. The
user needs database access to the other database but you
don't need to grant object permissions as long as the
database chain is unbroken. This includes the database
owners, not just the objects involved.
-Sue
On Mon, 21 Jun 2004 16:26:49 -0500, "Becker"
<ben@.benbecker.net> wrote:

>I have two databases, say A & B.
>I have a table in database A called Table 1.
>I have a view in database B called View 1.
>B.View 1 selects * from A.Table 1.
>Both the view and the table are owned by dbo.
>I create a new user and grant him select on B.View 1 and database permissio
n
>on only database B.
>When this user queries B.View 1, he gets an error saying he is not a user o
f
>database A. Why?
>I thought the chain of ownership for dbo for both table and view would allo
w
>this scenario to work? I don't want this user to be a member of the public
>role for database A, I only want him to be a member of database B.
>Any help would be appreciated!
>Thanks,
>Ben
>
>|||Sue,
Thanks for the response. My only problem is that the public profile in
database A has all kinds of privileges that I don't want this user to have
and I can't seem to revoke the public role for a user for a database? Seems
public must be granted to all users for a database that will access it? I'm
on SQL Server 7.
Thanks,
Ben
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:558fd057if2vl481dth6uot53cica0cuin@.
4ax.com...
> The user needs to be a valid user in both databases. The
> user needs database access to the other database but you
> don't need to grant object permissions as long as the
> database chain is unbroken. This includes the database
> owners, not just the objects involved.
> -Sue
> On Mon, 21 Jun 2004 16:26:49 -0500, "Becker"
> <ben@.benbecker.net> wrote:
>
permission[vbcol=seagreen]
of[vbcol=seagreen]
allow[vbcol=seagreen]
public[vbcol=seagreen]
>|||Hi Ben,
Yes...all users in a database are members of the public
role and you can't delete users out of the public role. If
all users accessing the database do not need all the
privileges then it may be better to move those permissions
out of public and into a user defined role.
-Sue
On Mon, 21 Jun 2004 22:33:50 -0500, "Becker"
<ben@.benbecker.net> wrote:

>Sue,
>Thanks for the response. My only problem is that the public profile in
>database A has all kinds of privileges that I don't want this user to have
>and I can't seem to revoke the public role for a user for a database? Seem
s
>public must be granted to all users for a database that will access it? I'
m
>on SQL Server 7.
>Thanks,
>Ben
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:558fd057if2vl481dth6uot53cica0cuin@.
4ax.com...
>permission
>of
>allow
>public
>|||I agree with Sue. Personally, I grant permissions only to user-defined
roles and never grant permissions to public.
If you must keep your public role permissions, you can create a role in
DatabaseA for your DatabaseB users and DENY the unwanted permissions to that
role. Remember that DENY always takes precedence over GRANT.
Hope this helps.
Dan Guzman
SQL Server MVP
"Becker" <ben@.benbecker.net> wrote in message
news:eVGlAnAWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> Sue,
> Thanks for the response. My only problem is that the public profile in
> database A has all kinds of privileges that I don't want this user to have
> and I can't seem to revoke the public role for a user for a database?
Seems
> public must be granted to all users for a database that will access it?
I'm
> on SQL Server 7.
> Thanks,
> Ben
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:558fd057if2vl481dth6uot53cica0cuin@.
4ax.com...
> permission
user[vbcol=seagreen]
> of
> allow
> public
>