Database role error on dbo objects

Question

Database role error on dbo objects

JackIntheBox

SSCrazy

Points: 2022
More actions
June 9, 2011 at 3:22 pm

#242044

Hello,
So I mapped my windows login to the new Database role which i created. I went into securables and granted access to the Schemas "Agent".
However i am know getting an error showing me that unable to access the underlying DBO objects. Does anybody have a clue where i would be missing something.?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Don Halloran SSCertifiable Points: 5937 More actions · Answer 1

Let me give you my understanding of what you have before I give my answer, so that if any of my assumptions are wrong you can correct them...

You have a bunch of objects (tables, procedures, etc) in the dbo schema.

You have also created a schema called "agent".

You have created objects in the agent schema that reference objects in the dbo schema. Perhaps a stored procedure or function that selects from dbo.whatever.

You have created a role which has select/execute permission on the agent schema, and your windows login is a member of that role.

You are getting permission denied errors for objects in the dbo schema when you execute/select/etc objects in the agent schema.

If all that is correct, you have an ownership chaining problem. If both schemas are owned by the same user, then what you are trying to do will work (without having to grant explicit permissions on the dbo schema to your new role). However, if the owners are different, you do need to grant explicit permissions.

One way to solve this in your case would be to give the schemas the same owner:

alter authorization on schema::agent to dbo

Here's a script to demonstrate the concept:

-- make some users, schemas and objects

create user u1 without login

create user u2 without login

create user u3 without login

go

grant create table, create view to u1, u2

go

create schema s1 authorization u1

go

execute as user = 'u1'

go

create table s1.t(i int)

go

revert

go

create schema s2 authorization u2

go

execute as user = 'u2'

go

create view s2.v as select i from s1.t

go

grant select on schema::s2 to u3

revert

go

-- at this point the s1 schema and the object s1.t are owned by u1

-- the s2 schema and the object s2.v are owned by u2

-- u3 has select on s2.v

execute as user = 'u3'

-- permission denied on s1.t when attempting the following

select * from s2.v

revert

go

-- ok, lets give both schemas the same owner and try again

alter authorization on schema::s1 to u2

go

execute as user = 'u3'

select * from s2.v

go

-- cleanup

revert

drop view s2.v

drop table s1.t

drop schema s2

drop schema s1

drop user u1

drop user u2

drop user u3

JackIntheBox SSCrazy Points: 2022 More actions · Answer 2

Nice example; However this should not have had happened in the first place; and seems kind of weird. We upgraded the DB with a newer version of our build and everything seems to work fine.You have got exactly right in trying to deduce my problem.however i made sure non of my schemas were owned by anyone else other than dbo. I will just elaboarte it a little bit as i felt my question wasnt that intuitive, so that others can take inference from this issue.

I had a error saying " The SELECT permission was denied on the object 'XXX', database 'X', schema 'dbo', when logged in as a Windows auth which was mapped to database role.

This is what i had followed; Everything was from GUI hence no script.

I added my self as login at the server level; user mapping to DB 'X'

Created a role 'AgentAccess'; having access to select schemas "Agent". Assigned Select GRANT to the schema for that role .

Went in the user properties assigned the 'AgentAccess' in the DB role membership ; went in the securables and assigned access to schemas and select GRANT.

I have not touched the Schemas and their authorization was still 'dbo.'

Do you think i missed any steps in here; and what could be causing the issue before?

Thanks for you inputs

Don Halloran SSCertifiable Points: 5937 More actions · Answer 3

I'm actually really bad with the GUI since I type stuff out 99% of the time, so I'm afraid I don't feel confident commenting on the steps you took. It looks right to me, are you absolutely sure the agent schema is owned by dbo (ie, did you run the alter authorization just to be safe?)