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
chrisIericksonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
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

chrisIericksonAuthor Commented:
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

chrisIericksonAuthor Commented:
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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

cyberkiwiCommented:
I guess nr_facilities owns nr_facilities ?  (ALTER AUTHORIZATION ON SCHEMA::[nr_facilities ] TO [nr_facilities])
And you have a user in role map_3d_ro  (sp_addrolemember 'map_3d_ro', 'testuser')
And you log in as testuser, but this doesn't work?

select * from nr_facilities.building_inspection

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chrisIericksonAuthor Commented:
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?
cyberkiwiCommented:
What do you use to do this? "query what tables are in the database."
chrisIericksonAuthor Commented:
i tried granting the 'view definition' schema permission along with 'control'.  neither worked.
chrisIericksonAuthor Commented:
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
cyberkiwiCommented:
I see. I thought it was a permission to the table.
Now for that other issue... have a look here

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/d7b29597-8d26-42e3-8db9-0a75ac9f2de2
chrisIericksonAuthor Commented:
That fixed SSMS.  Now for Map3d -- still seeing the problem.
cyberkiwiCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
CAD/Architecture Software

From novice to tech pro — start learning today.