Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want an easy way to make a read-only user on my database. The user should have permission to execute stored procedures only if they don't modify data. I don't want to figure out which stored procedures are read-only and give permissions that way because there are far too many to go through.

I thought this was possible after I read the following:

"A stored procedure can require both EXECUTE permission on the stored procedure and INSERT permission on several tables that are referenced by the stored procedure." --

Unfortunately, I am able to EXECUTE an 'executable' stored procedure that INSERTs into a 'read-only' user table.

share|improve this question
up vote 1 down vote accepted

That comment on MSDN simply means that there are some situations (such as sometimes when using dynamic SQL) where granting a user EXECUTE on the stored procedure will not necessarily be enough to allow them to execute it.

What you want cannot be done - if they are able to run a stored procedure, they will be able to do whatever the stored procedure does - INSERT, DELETE, UPDATE, whatever. Even SELECTs can make changes via SELECT INTO. And there's always temporary tables or table variables to complicate any attempts to identify a list of safe/unsafe sps.

My advice is to narrow down as much as possible what the read-only user should actually need to read, create a db role with EXECUTE permissions to just those objects, and start from there. And I hope you've not been giving EXECUTE permissions to the Public role, because if so you'll have to DENY EXECUTE on all the other stored procedures as well.

share|improve this answer

Unfortunately (as far as I know) there is no way to do this. Giving a user (even a read only user) the ability to execute a stored proc means that they will be able to execute anything that's inside that sproc.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.