'EXECUTE PERMISSION DENIED' on executing Sproc

[Mike L.]

Hi,

I got this 'EXECUTE permission denied on object <mySproc>' error message
everytime I try executing my SQL server Sproc.
What's this and how to fix this err?

many thnaks in advance,
mike

 

[Tod Birdsall]

Hi Mike,

Technically, this is the wrong group for this question. You will
probably have better luck posting in the following news group :
http://groups-beta.google.com/group/microsoft.public.sqlserver.programming

The following news group thread will probably help :
http://tinyurl.com/3ohj6

Tod Birdsall, MCP
http://tod1d.blogspot.com

 

[Mike L.]

Hi Tod,

Is it? So it's got nothing to do with my ASP.NET settings/configurations or
whatever?
anyone else can help me with this?

thks,
mike

 

[Eliyahu Goldin]

Mike,

This is a database user right issue. Try first to get it working in your
database client tools with the same database authentication you are using in
your asp.net program.

Eliyahu

 

[Juan T. Llibre]

Hi, Mike.

Actually, it does *not* have anything to do
with ASP.NET's settings/configurations.

It has to do with SQL Server's security settings.

Tod gave you a link with a good pointer to the answer.

The answer is to allow execution permission for the Sproc,
to the account you're using to work with your SQL server DB.

That will depend on how you've configured SQL Server,
and which account ASP.NET uses to work with SQL Server,
which in turn depends on which version of IIS you're using.

You did not mention whether you're using Windows security,
or SQL Server security, or mixed-mode security, in SQL Server,
and you did not mention which version of IIS you're running,
and you did not mention which OS you're using, and you did
not mention which version of ASP.NET/NET Framework
you're using.

Posting that information would have helped to
give you a straighter, more accurate, answer.

This is common in these newsgroups.

Everybody who posts a question here should, at the very least,
identify their OS, the version of IIS, and their .NET Framework
version/ASP.NET version. Additionally, if the question involves
database access, the version of their db platform.

That will help those who are trying to help the poster find the
answer quickly, and get the right answer back to the poster faster.

So, check which account you're using to retrieve/write data
from/to SQL Server, and give execute permission on the sproc
to that account.

The account will be either the SQL Server login you're using to retrieve
data from SQL Server (look in your connection string to know which
one it is), or the ASPNET account if you're running IIS 5, or the
Network Service account if you are running IIS 6.

Only you know which account you're using.



Juan T. Llibre
ASP.NET MVP
===========

 

[Mike L.]

Thanks Juan,

Yes, I forgot to mention that I got this prob on one of my hosted
application.
So, i don't have any privilege needed to change any permission on any of my
SQL objects.
Aren't all we have just login name, pwd and DB for which belong to us in
such hosted environment?
Anyway, my hosting server runs Win2K3, SQL Server 2K, .NET framework 1.1

thks & brgds,
mike

 

[Juan T. Llibre]

re:

So, i don't have any privilege needed to change
any permission on any of my SQL objects.

You can request your ISP to make the permission change.



Juan T. Llibre
ASP.NET MVP
===========

 

[Norman Yuan]

Loggin (authentication) and permission (authorization) are different thing
Authentication just proves who you are. Once your identity is proven, the
system will decide what you can do/vannot do, according to your identity.
You may be able to logged in but cannot do anything if no permission is
given. In your case, you have logged in, but you are not given the
permission to use that Sproc (and maybe more). If you desgned the ASP.NET
system, you need to study more on the security issue. Or ask the SQL Server
administrator to give you (or the ASP.NET user) permission to use that Sproc
(and others).