Stored proc selecting from tables in two schema

Question

Post reply

Stored proc selecting from tables in two schema

Roy Ernest

SSC-Dedicated

Points: 38780
More actions
November 27, 2012 at 10:16 am

#285449

Hi All,
I have a very small problem. I have two schema in my database. The default "dbo" and user created "abc". I have a stored proc that is created in "abc" schema that selects from tables in the schema "dbo" and "abc". I want to give execute permission to this stored proc to a role. How can I do this without having to use "Execute AS Owner" or giving direct select permission to the role?
Anyone can help me in this?
-Roy

Viewing 10 posts - 1 through 9 (of 9 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Do you use fully qualified objects names in your procedure for all objects from all schemas including dbo?

If yes, "grant exec on [procname] to [rolename]" should work...

If no, then change your proc to use fully qualified objects names, otherwise you are going to face some issues...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

Roy Ernest

SSC-Dedicated

Points: 38780

November 27, 2012 at 11:01 am

#1562775

It is fully qualified...

-Roy

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

grant exec on [schema].[procname] to [rolename]

go

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

That does not work. It gives permission error on the table in the other schema. It works with "Execute as Owner"

-Roy

GSquared SSC Guru Points: 260824 More actions · Answer 5

Is there an active Deny in place on that role accessing the second table or the schema it's in? That works differently than merely missing a permission.

Another alternative, create a user that has the needed access to both objects/schemas, and execute as that user instead of as owner. Can get tricky to get it right, but it does work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 6

I can make it work by giving the Select permission to the role on the other Schema. I dont want to give direct select permission on tables.

-Roy

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

Roy Ernest (11/27/2012)
That does not work. It gives permission error on the table in the other schema. It works with "Execute as Owner"

What user do you use to execute GRANT EXEC? It's better to be dbo...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 8

I am going to try

GRANT SELECT ON SCHEMA :: abc to abcRole WITH GRANT OPTION

and see if that will work.

-Roy

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

That worked. I needed to give Permission to the schema. Thats it. Thanks everyone for helping.

-Roy