EXECUTE permission was denied

Question

EXECUTE permission was denied

Mani-584606

SSChampion

Points: 11788
More actions
April 17, 2014 at 10:38 am

#285882

Developer getting EXECUTE permission was denied error as below:
Msg 229, Level 14, State 5, Line 2
The EXECUTE permission was denied on the object 'myobject', database 'Mydb', schema 'Myschema'.
I tried to find the object in Mydb database but I did not find it. How to find that object, what it is and grant execute permission?

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Mani I know you obfuscated the error, but i'll bet you an internet cookie that the procedure being called references a different database or different schema than what the procedure belongs to.

it sounds like ownership chaining is being broken to me.

for example, if dbo.myprocedure , which exists in the database [SandBox], references ANY OTHER database, the calling user will get the error you describe.the same can happen if it references a different schema, like [SandBox].Finance.Tablename instead of [SandBox].dbo.Tablename, but that's a little rarer to encounter in my experience; most places avoid multiple schemas.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tony Sweet Ten Centuries Points: 1049 More actions · Answer 2

I tried to find the object in Mydb database but I did not find it. How to find that object, what it is and grant execute permission?

If you cant see it you probably do not have access to it. You will need to get someone with db_owner or sysadmin or something like that to find the object if you can not find it. Then grant the developer access to run it.

USE AdventureWorks2012;

GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo

TO Recruiting11;

GO

- Tony Sweet

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 3

If the error is happening because a proc is called inside another proc (or something like that) and you have permissions to see the proc, running this little code snippet might help:

EXECUTE sp_MSforeachdb @Command1 = 'SELECT DB_NAME() AS DatabaseName, name AS ProcName FROM sys.objects WHERE name = ''MyProc''';

This assumes the proc is on the same Instance, not being referenced by a linked server.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.