Permission denied

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gudni G. Sigurdsson - 29 Feb 2008 09:11 GMT

Hi.
I am developing a VS2005 C# application accessing a SQL Server 2005 database
with Windows Integrated Security. Some of the users running this app are
connecting via login, e.g. Domain\ThisUser, with roles public and db_reader.
When my app tries to execute a stored function which only returns an integer
value and does not alter anything in the database, I get a
System.Data.Sqlclient.SqlException: EXECUTE permission denied on object ...
If the login belongs to e.g. the db_owner role everything runs OK.
Obviously, I do not want to grant such a privilege to all users of my app.
What should I do?
Is the solution to my problem having the application executing the
sp_setapprole stored proceedure to (temporarily) change the role membership.
This seems rather clumsy to me, though.
Any comments will be appreciated.
Regards,
Gudni

Reply to this Message

Gudni G. Sigurdsson - 29 Feb 2008 11:08 GMT

Hi.
Maybe I found the proper solution. For the functions in question, I set
datbase role public and granted execute permission. Now, an unprivileged
user can at least run my app. I would nevertheless be interested in having
comments on this.
regards,
Gudni

> Hi.
> I am developing a VS2005 C# application accessing a SQL Server 2005 database
[quoted text clipped - 12 lines]
> Regards,
> Gudni

Reply to this Message

Dan Guzman - 29 Feb 2008 12:21 GMT

Users will need permissions on all objects accessed directly by your
application. It's generally best to grant permissions only to roles and
then control access via role membership since this simplifies
administration.

Personally, I prefer to create my own custom roles rather than use the
built-in db_datareader and db_datawriter roles since that provides the most
flexibility. If you want to grant EXECUTE permissions to all objects in a
schema rather than individual objects, you can simply use:

GRANT EXECUTE ON SCHEMA::dbo TO MyRole;

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi.
> Maybe I found the proper solution. For the functions in question, I set
[quoted text clipped - 27 lines]
>> Regards,
>> Gudni

Reply to this Message