Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

EXECUTE permission was denied Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 10:38 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:47 PM
Points: 587, Visits: 1,998
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?
Post #1562772
Posted Thursday, April 17, 2014 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 12,889, Visits: 31,844

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1562784
Posted Thursday, April 17, 2014 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 6:38 AM
Points: 33, Visits: 65
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
Post #1562794
Posted Friday, April 18, 2014 6:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 7,133, Visits: 6,294
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562998
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse