2

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.

Mindy
  • 117
  • 2
  • 2
  • 8

2 Answers2

1

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.

MartW
  • 1,305
  • 10
  • 15
0

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.

Dave Holland
  • 1,898
  • 1
  • 13
  • 17