We have a SQL Developer Group.. We want to give them access to execute functions and sps but not to any that amends data in any way..

We currently only have the DBO schema and all objects sits on that schema.. We have a db_execute role. This role currently have execution rights on our objects on dbo...

We have tried creating a different role and tested by giving execution rights to one particular SP and assigning the dev group to the new role.. but when we gave access to the role.. it gave them rights to all objects that are executable again...

Is it possible to create another role, just for developers that will allow only retrieving of data and no amendments by not having to move the objects to a different schema...

Any help will be highly appreciated!

  • I'm confused. Just remove them from the db_execute role and add them to the new role that you create. Isn't that enough? I think you need to clarify, expand a bit further and detailed. – Tibor Karaszi Sep 8 '20 at 12:03
  • No it is not possible. Permissions are not so precisely defined for stored procedures. – SMor Sep 8 '20 at 12:30
  • add them to the db_denydatawriter role, explicit deny is applied last and overrides both explicit and implicit grants. Note that any procedure that does "EXECUTE AS [X]" will bypass that deny.... But you might check for multi-group permissions as well. – Jonathan Fite Sep 8 '20 at 12:31

Your Answer

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

Browse other questions tagged or ask your own question.