Cross-Database View Permissions

I have a view in database1 that uses views in database2.
My DBA says I must give users DATA READER permissions to
the tables referenced by the database2 views. I am trying
to keep customers OUT of the tables, only allowing access
thru views.
Do I really have to give DATA READER to the tables in
database2 to get my cross-database view references to work?It depends on whether you allow cross database ownership chaining. This is a
configurable option post SP3 wheras prior to that it was implicit. Basically
if all the views and tables have the same owner e.g. dbo and the databases
themselves are owned by the same login e.g. sa then users with permissions
to select from a view in database1 don't need any explicit permissions on
objects in database2 - they do still require access though.Post SP3 this is
configured using sp_configure 'Cross DB Ownership Chaining' and sp_dboption
with the 'db chaining' option. See BOL for more on cross database ownership
chaining.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Barnes" <anonymous@.discussions.microsoft.com> wrote in message
news:13a2e01c44408$5da79f30$a501280a@.phx
.gbl...
> I have a view in database1 that uses views in database2.
> My DBA says I must give users DATA READER permissions to
> the tables referenced by the database2 views. I am trying
> to keep customers OUT of the tables, only allowing access
> thru views.
> Do I really have to give DATA READER to the tables in
> database2 to get my cross-database view references to work?|||To add to Jasper's response, both databases need to be owned by the same
login in order for the dbo-owned object ownership chain to be unbroken.
Your users still need a security context in database2 but no permissions or
role memberships need to be granted if objects are only accessed indirectly
via referencing database1 objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Barnes" <anonymous@.discussions.microsoft.com> wrote in message
news:13a2e01c44408$5da79f30$a501280a@.phx
.gbl...
> I have a view in database1 that uses views in database2.
> My DBA says I must give users DATA READER permissions to
> the tables referenced by the database2 views. I am trying
> to keep customers OUT of the tables, only allowing access
> thru views.
> Do I really have to give DATA READER to the tables in
> database2 to get my cross-database view references to work?

Cross Join without Table?

I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:

tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk

I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end

But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:

event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown

Thanks for any help,
jbDear Lord, save us from those who would require us to eat sphagetti with chopsticks, swim with boxing gloves, and develop databases without stored procs or temporary tables. And forgive them, for they know not what the hell they are doing.

Fortunately for you, it is probably possible to get a reasonable solution for your problem even without using stored procs or temp tables. :(

I think this will work...

Select SubQuery.event_pk, isnull(SubQuery.myCount, 0) as myCount, RegTypes.countCat
From (Select 'Employer' as countCat
UNION
Select 'Individual' as countCat
UNION
Select 'Both' as countCat) RegTypes
Left outer join
(Your Query Goes Here) SubQuery
on RegTypes.countCat = RegTypes.countCat|||You're to best. Didn't realize you could "create" a derrived table without selecting at least one field from a real one. Thanks so much.

It is by coffee alone I set my mind in motion. It is by the beans of java that thoughts acquire speed. The hands acquire shaking. The shaking is a warning. It is by coffee alone I set my mind in motion.

Cross Join Without Table?

I have the following structure with remote select permissions; I cannot
create temp tables or use stored procs:
tblEvent with event_pk, eventName
tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories:
case
when c.person_fk is Null and c.organization_fk is not null then 'Employer'
when c.person_fk is Not Null and c.organization_fk is null then 'Individual'
when c.person_fk is not Null and c.organization_fk is not null then 'Both'
else 'Unknown'
end
But I need some kind of count (0) for every category. I've used a
cross-join, group by in the past - but what do you do if you don't have a
table? For example, the end result when selecting event_pk=(112,113) would
be:
event_pk, myCount, countCat
112 0 Employer
112 1 Individual
112 4 Both
112 0 Unknown
113 5 Employer
113 0 Individual
113 0 Both
113 2 Unknown
Thanks for any help,
jbYou can use a derived table construct with a set of numbers. Your
requirements are not very clear from the narrative, can you post your table
structure & sample data along with expected results? For details refer to:
www.aspfaq.com/5006
Anith