ownership chains, users and views

We have a design using views to partition visibility of table rows by sql us
er. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SALE
S_PERSON='Fred'
and
CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SA
LES_PERSON='Barney'
Neither salesman should be able to select from ALL_SALES_LEADS and each, bei
ng amazingly SQL savvy salesmen, can log on to sql and execute SELECT * FROM
SALES_LEADS WHERE STATUS='New'.
Problem is that neither because neither Fred nor Barney owns the ALL_SALES_L
EADS table, they can't select off the view with any combination of GRANT/DEN
Y/REVOKE statements that we can find because the ownership chains are broken
.
Our best fallback is to name the views differently but leave them owned by d
bo. Not as neat and tidy. ie
CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
Is there any way around our original problem, SQL gurus? I think this would
be a very good use of user-owned views in a number of realistic scenarios.> Our best fallback is to name the views differently but leave them owned by
dbo.
The table and view need to have the same owner in order to use views as a
security mechanism. If each salesperson accesses the database with their
own userid, you might consider filtering using database userid instead of a
hard-coded constant. This way, you only need one view. For example:
CREATE VIEW dbo.SALES_LEADS AS
SELECT *
FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON = CURRENT_USER
GO
GRANT SELECT ON dbo.SALES_LEADS TO SalesRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave Cattermole" <anonymous@.discussions.microsoft.com> wrote in message
news:726B0928-E93C-4233-A5FE-051E8ADA5684@.microsoft.com...
> We have a design using views to partition visibility of table rows by sql
user. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
> CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
> and
> CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Barney'
> Neither salesman should be able to select from ALL_SALES_LEADS and each,
being amazingly SQL savvy salesmen, can log on to sql and execute SELECT *
FROM SALES_LEADS WHERE STATUS='New'.
> Problem is that neither because neither Fred nor Barney owns the
ALL_SALES_LEADS table, they can't select off the view with any combination
of GRANT/DENY/REVOKE statements that we can find because the ownership
chains are broken .
> Our best fallback is to name the views differently but leave them owned by
dbo. Not as neat and tidy. ie
> CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON='Fred'
> GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
> Is there any way around our original problem, SQL gurus? I think this
would be a very good use of user-owned views in a number of realistic
scenarios.

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
>

Ownership Chains and Linked Servers

I have 2 servers which are linked. All objects on server1 are owned by sa an
d
all objects on server2 are owned by sa. I have created a mapping between sa
on server1 and sa on server2. However the ownership chain seems to be broken
when I try and run a stored procedure from server1 which accesses objects on
server2 as a user other than sa.
Any ideas?Ownership chaining does not apply to queries across linked servers because
these are essentially different database connections.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@.discussions.microsoft.com> wrote in message
news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@.microsoft.com...
>I have 2 servers which are linked. All objects on server1 are owned by sa
>and
> all objects on server2 are owned by sa. I have created a mapping between
> sa
> on server1 and sa on server2. However the ownership chain seems to be
> broken
> when I try and run a stored procedure from server1 which accesses objects
> on
> server2 as a user other than sa.
> Any ideas?|||Hmm. That's what I feared.
Does this mean that the ownership chain will always be broken across linked
servers and so I have to give a user server2 select permisssions so that a
stored procedure on server1 is able to select from server2?
"Dan Guzman" wrote:

> Ownership chaining does not apply to queries across linked servers because
> these are essentially different database connections.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Stevo" <Stevo@.discussions.microsoft.com> wrote in message
> news:02DAAFDA-584E-4148-8B26-A752D6D08A7F@.microsoft.com...
>
>|||> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
Your understanding is correct that linked servers effectively break the
ownership chain. You'll need to grant permissions needed on server2 to the
account(s) used for the linked server connection. See sp_addlinkedsrvlogin
in the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stevo" <Stevo@.discussions.microsoft.com> wrote in message
news:AAFC2AE4-08BF-486A-A712-1BE6072B8DB0@.microsoft.com...[vbcol=seagreen]
> Hmm. That's what I feared.
> Does this mean that the ownership chain will always be broken across
> linked
> servers and so I have to give a user server2 select permisssions so that a
> stored procedure on server1 is able to select from server2?
> "Dan Guzman" wrote:
>