0

At the Application_Start of my web site I execute a stored procedure to read some data from one SQL Server 2008 database.

This is the only call to this database.

I would create a specific SQL Server user, with read-only permission, to exec this sp.

I created a new SQL Server user with db_datareader role, but when I exec the sp I have an error at this line:

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

The error is: EXECUTE permission denied on object ...

How can I workaround this ?

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
opaera
  • 1
  • I understand that you have created a new user, given him the `db_datareader` role, and granted him the permission to execute the SP. Have you considered granting him the right to connect to your DB? As odd as it sounds, connection permissions are required for a user to connect and do whatever against a database. – Will Marcouiller Nov 08 '10 at 17:21
  • I didn't do that but it seems working becuase it opens the connection and then performs ExecuteReader. I'm trying granting the user but it doesn't work. Please see the comment to the answer. Thanks. – opaera Nov 08 '10 at 17:52

1 Answers1

2

Have you granted the user you created access to the stored procedure?

GRANT EXECUTE ON sp_your_proc TO your_user;
Erik
  • 714
  • 11
  • 29
  • Thanks for the answer. I tried now but I receive an error. It's impossible to add, deny .. authorizations to sa, dbo, .... Sorry but I receive the error message in Italian. The user I created is under the dbo schema and has only the db_datareader role. – opaera Nov 08 '10 at 17:01
  • Does the user have access to the dbo schema? How did you create the user? If you posted your error message, maybe I could read something from it. Also, if you have access to SQL Server Management Studio, you could examine the properties of the db login under Security/Logins. – Erik Nov 09 '10 at 09:27