"EXECUTE permission was denied" error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

"EXECUTE permission was denied" error

Author

Topic

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 01:14:17

Im having problems executing a stored proc...

here's what i did:
1. create a stored procedure using sql server management studio. it was successfully created.
2. i then tried executing the newly created sp and here's what i get:

The EXECUTE permission was denied on the object 'xxxxxxx', database 'zzzzzzz', schema 'dbo'.

what shall i do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-11 : 02:05:31

Where did you get the error? Inside SSMS or in your front-end application?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 05:14:38

inside SSMS...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 05:42:27

Under which user context?
Do the user have execute to the SP?

E 12°55'05.25"
N 56°04'39.16"

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 05:45:31

im not sure what you mean by under which user context?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 05:55:19

Which user did you use to create the SP?
Which user did you use to execute the SP?

What are the EXECUTE permissions for the SP?

E 12°55'05.25"
N 56°04'39.16"

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 06:05:43

i created and executed SP under the same user...

as per the EXECUTE permission for the SP... i tried checking it by looking at the properties of the SP... and there i saw that on the permission tab there are nothing in there.. when i tried adding a user/roles it doesnt give me any error message nor did i successfully grant any roles....

ive checked also the effective permissions... there were only two, ALTER and REFERENCES.....

..... btw, my SP is just a select statement with 2 parameters...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:12:56

[code]SELECT o.TYPE_DESC,
o.NAME AS OBJECTNAME,
dp.PERMISSION_NAME,
dp.STATE_DESC,
p.NAME
FROM SYS.DATABASE_PERMISSIONS AS dp
INNER JOIN SYS.OBJECTS AS o ON o.OBJECT_ID = dp.MAJOR_ID
INNER JOIN SYS.DATABASE_PRINCIPALS AS p ON p.PRINCIPAL_ID = dp.GRANTEE_PRINCIPAL_ID
WHERE dp.CLASS = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 06:17:34

quote:

Originally posted by Peso

SELECT		o.TYPE_DESC,
		o.NAME AS OBJECTNAME,
		dp.PERMISSION_NAME,
		dp.STATE_DESC,
		p.NAME
FROM		SYS.DATABASE_PERMISSIONS AS dp
INNER JOIN	SYS.OBJECTS AS o ON o.OBJECT_ID = dp.MAJOR_ID
INNER JOIN	SYS.DATABASE_PRINCIPALS AS p ON p.PRINCIPAL_ID = dp.GRANTEE_PRINCIPAL_ID
WHERE		dp.CLASS = 1

E 12°55'05.25"
N 56°04'39.16"

am i suppose to run this?

anyway, i did... and theres no output at all... just the column names without any rows...

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 06:20:05

i think there are really no permissions at all... problem is... how can i grant "myself" to execute the SP i created.... logically, if i was the one who created the SP, i can execute it... am i right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 06:21:51

Have a look at GRANT EXECUTE in Books Online.

E 12°55'05.25"
N 56°04'39.16"

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 06:31:34

i've checked online and tried... but im getting this error..

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

my question is... if i was the one who created the SP, shouldn't i be allowed to make the necessary changes and granting power over it? or the administrator (whatever) has to do it for me?

mind_invader
Starting Member

24 Posts

Posted - 2008-03-11 : 06:35:19

@Peso

im really sorry... i feel dumb not knowing these things i've been asking you... its just that im really knew to this...

i really appreciate you taking time to answer my questions... thanks!

i just hope i can solve this problem now... im still in the learning process and hope to learn more from this forum....

Sanjana
Starting Member

1 Post

Posted - 2008-06-11 : 19:22:24

Hi mind_invader,

Could you solve the problem ?
I have the same issue. I have created a stored procedure but when I execute it, i get the same error as yours.

Thanks in Advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 01:38:10

quote:
Originally posted by Sanjana

Hi mind_invader,

Could you solve the problem ?
I have the same issue. I have created a stored procedure but when I execute it, i get the same error as yours.

Thanks in Advance!

What is the db role under which your login is added to database? I assume you are connecting to sql server using windows authentication.

aji
Starting Member

1 Post

Posted - 2009-01-17 : 04:58:42

right click on the database -->Data base properties-XXXXXX(database)-->click on permissions-->Explicit permissions for user--please check execute on the grant column
this worked for me ..i am using msql server 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-17 : 06:34:17

quote:
Originally posted by aji

right click on the database -->Data base properties-XXXXXX(database)-->click on permissions-->Explicit permissions for user--please check execute on the grant column
this worked for me ..i am using msql server 2005

or you can even use GRANT... T-sql statement

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources