Ado.Net ExecuteReader with a read-only SQL Server user

Question

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

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
– Will Marcouiller, Commented Nov 8, 2010 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
– opaera, Commented Nov 8, 2010 at 17:52

Erik · Accepted Answer · 2010-11-08 16:49:50Z

2

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

GRANT EXECUTE ON sp_your_proc TO your_user;

answered Nov 8, 2010 at 16:49

Erik

71411 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

opaera Over a year ago

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.

Erik Over a year ago

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.

Collectives™ on Stack Overflow

Ado.Net ExecuteReader with a read-only SQL Server user

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related