10

I would like to set up a "reading" user on my SQL Server 2012 instance. I would like to allow him the right to execute any stored procedure, or function, or direct SQL statement, which retrieves data from all tables and views, but not to update or to insert (i.e. read anything and write nothing).

Can I set this up without specifically giving rights to each function or stored procedure by name, but rather grant him execution right on any function or stored procedure, just taking away the right to modify tables?

Will anything change if I run SQL Server 2008 instead?

Clarifications and additions:

  1. If a stored procedure changes data, the user should receive an error message (either denying the modification or refusing access to the stored procedure completely).
  2. If a potential solution involves denying permissions, can I just not grant certain permissions instead of denying?
  3. Can a deny be applied on all tables, views etc. (existing now and in the future) in the database in one statement?
  • I am new here, please feel free to set tags as appropriate and otherwise to edit the question – gt6989b Dec 8 '14 at 19:14
  • 1
    @KrisGruttemeyer that was the thought - he could execute it but it would result in an error of some sort; alternatively i am ok with him not being allowed to execute it, as long as i don't have to specify a list of sp's he has privileges to run – gt6989b Dec 8 '14 at 19:38
  • 1
    In that case you need to read about SQL Server permissions. There are 3 permission variants - GRANT to give permissions, DENY to deny permissions, and REVOKE to remove a GRANT or DENY. Without a GRANT or DENY the user can inherit permissions, say from getting access to a stored procedure. – JNK Dec 8 '14 at 19:43
  • 1
    @gt6989b If you DENY DELETE, INSERT, UPDATE on a database or schema I believe it will only affect tables and views. – JNK Dec 8 '14 at 19:48
  • 1
    @gt6989b - Just noting that that the suggested answer in these comments turned out to be incorrect. See Paul White's response. – RLF Dec 10 '14 at 18:41
14

This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start:

-- The user-defined role containing all required permissions
CREATE ROLE Readers AUTHORIZATION dbo;

-- Give read-only access to all tables,
-- views on those tables, and in-line
-- functions
ALTER ROLE db_datareader ADD MEMBER Readers;

-- Example: add a user (Bob) to the role
ALTER ROLE Readers ADD MEMBER Bob;

After this, Bob will have wide read-only privileges in the database. He will be able to read from all tables, views on those tables, and in-line functions. He will not be able to execute any procedures or use non-inline functions, however.

You will need to grant specific permissions to the Readers role for safe functions and procedures you wish Readers to have access to. There may be steps you can take to make this process easier (such as grouping objects into a schema and granting execute permission on the schema instead of individual objects) but there are too many details to cover here.

One thing to be aware of is that the lack of data modification privileges would not prevent a Reader from changing data via a stored procedure she has been granted execute permission on, if the procedure and object being modified share a common owner. Nor would an explicit deny be respected in this case. This feature is known as Ownership Chaining.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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