Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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." -- http://technet.microsoft.com/en-us/library/ms191291.aspx

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

share|improve this question
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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.