asp.net :Execute permission denied on object 'SprocName'

[sujal]

I have an ASP.NET application that uses Integrated Windows authentication. My Web.config looks like below

<add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" />

When users try to access my application, they get the below error:

Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo'

The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse.

Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO.

Thanks,

 

[jbenson001]

When the proc was created, were EXECUTE permissinons granted to public?

Jim

 

[sujal]

I don't remember granting any permission when I created the Sproc. How to enable that now?

Also it should allow only 'Windows authenticated users' who access the system and not public.

 

[jbenson001]

Grant execute on <sp name> TO public


The restricton to who can access it should be done in your front end app. If the user is not authenticated they can't execute it, from the app anyway.

 

[sujal]

Thanks. How can I grant this on all objects on the DB

 

[jbenson001]

Glad to help.

You should only have to worry about granting execute permissions to each SP.