asked on

SQL Server grants on non-dbo owned schemas

Hello,
I have SQL Server (2008) database that has several schemas:

Schema1 - owned by dbo user.
Schema2 - owned by schema2owner user.

I've created a database role (role1) which is then granted to the user (roleUser).

I've then granted select on all of the tables in (schema1) and (schema2) to the role (role1) as such:
grant select on schema1.table1 to role1;
grant select on schema2.table1 to role1;

When I login as roleUser, I can only see the tables in schema1 (owned by dbo).

So I tried:
grant select on schema1.table1 to role1;
execute as user='schema2Owner';
grant select on schema2.table1 to role1;

and

grant select on schema1.table1 to role1;
grant select on schema2.table1 to role1 as schema2Owner;

but no matter what, i cannot see the table(s) from the second schema as roleUser.

I've also tried granting schema2 permissions to role1, but am still getting nowhere.

What am I mssing

cyberkiwi

Are you sure you have all the steps correct?
Try the attached - run each line individually.
For the last part, log on again to the server as John1

create database test2
use test2;
create schema bob;
exec sp_addlogin 'john1', 'doe123';
exec sp_adduser 'john1';
exec sp_addlogin 'jane1', 'doe123';
exec sp_adduser 'jane1';
create table dbo.tx (  i int);
create table bob.tx(  i int);
ALTER AUTHORIZATION ON SCHEMA::[bob] TO [jane1];
create role testrole;
EXEC sp_addrolemember N'testrole', N'john1';
grant select on bob.tx to john1;
grant select on dbo.tx to john1;

-- log in as John1 / doe123
select * from bob.tx
select * from dbo.tx

Open in new window

chrisIerickson

ASKER

I guess I abstracted from teh real database, which has 6 schemas and 30 tables. I can't re-create it, as it is in use.

Here are my actual grants:

create role map_3d_ro;

execute as user='nr_facilities'
grant select on nr_facilities.building_inspection to map_3d_ro;
grant select on nr_facilities.improvement_inspection to map_3d_ro;
grant select on nr_facilities.work_order to map_3d_ro;

execute as user='nr_fsp'
grant select on nr_fsp.activity to map_3d_ro;
grant select on nr_fsp.managed_area to map_3d_ro;
grant select on nr_fsp.managed_area_point to map_3d_ro;
grant select on nr_fsp.managed_area_stand to map_3d_ro;
grant select on nr_fsp.plan_information to map_3d_ro;

execute as user='nr_ism'
grant select on nr_ism.infestation to map_3d_ro;
grant select on nr_ism.infested_area to map_3d_ro;
grant select on nr_ism.treatment to map_3d_ro;

execute as user='dbo'
grant select on og_dot.structure to map_3d_ro;
grant select on og_dot.structure_outline to map_3d_ro;

grant select on og_pipeline.anode to map_3d_ro;
grant select on og_pipeline.asset_report to map_3d_ro;
grant select on og_pipeline.centerline_segment to map_3d_ro;
grant select on og_pipeline.foreign_line_crossing to map_3d_ro;
grant select on og_pipeline.ground_bed to map_3d_ro;

grant select on og_surfaceland.building_residence to map_3d_ro;
grant select on og_surfaceland.land_disturbance to map_3d_ro;
grant select on og_surfaceland.pipeline to map_3d_ro;
grant select on og_surfaceland.roads_trails to map_3d_ro;
grant select on og_surfaceland.utility_line to map_3d_ro;
grant select on og_surfaceland.water_body to map_3d_ro;
grant select on og_surfaceland.well to map_3d_ro;
grant select on og_surfaceland.well_buffer to map_3d_ro;
grant select on og_surfaceland.wildlife_point to map_3d_ro;

execute as user='reference'
grant select on reference.county to map_3d_ro;
grant select on reference.jonah_leases to map_3d_ro;
grant select on reference.jonah_wells to map_3d_ro;
grant select on reference.lake to map_3d_ro;
grant select on reference.local_road to map_3d_ro;
grant select on reference.major_road to map_3d_ro;
grant select on reference.pipe_right_of_way to map_3d_ro;
grant select on reference.pipeline to map_3d_ro;
grant select on reference.plss_section to map_3d_ro;
grant select on reference.plss_township to map_3d_ro;
grant select on reference.quad to map_3d_ro;
grant select on reference.river to map_3d_ro;

Open in new window

chrisIerickson

ASKER

on the above, the og_* schemas are owned by dbo, and I can access them just fine. However, the nr_* and reference schemas are owned by users with their same respective names. I've granted it using all the aformentioned methods but can never see them.

I can change the schema owners to dbo, and it works, but that breaks a ton of existing permissions, which I'm trying to avoid.

There must be something more here about schema owners and permissions that I don't understand.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrisIerickson

ASKER

OK, good point. I can query the table. I just can't see the table when I query what tables are in the database.

Is that a separate permission?

cyberkiwi

What do you use to do this? "query what tables are in the database."

chrisIerickson

ASKER

i tried granting the 'view definition' schema permission along with 'control'. neither worked.

chrisIerickson

ASKER

Well, 2 methods:
1) Connect via sql mgmt studio - doesn't list the tables in explorer
2) (the more important one in my case) connect via. autodesk map 3d (FDO) -doesn't list the tables

SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrisIerickson

ASKER

That fixed SSMS. Now for Map3d -- still seeing the problem.

cyberkiwi

Hi there,

I've reached the end of my expertise here. SQL Server I'm ok - Autodesk Map 3d (quick?)...
Maybe you should edit the question zones and put it in there.

As far as SQL Server side, I think that's all sorted.

Good luck