Re: exec permissions

Andre (andre@xxxxxxxxxx) writes:
If I want a group of developers to have access to see the variables in
stored procs, and be able to execute them, what permission do I need to
give the users? I don't want to make them all dbo's, nor do I really
want to grant exec on the stored procs to every user. It seems like
there has to be a better way but I don't see a role that fills this
need. I'm sure I'm overlooking something.

First create a role with CREATE ROLE, and then add the developers to that
role with sp_addrolemember.

Then you grant that role EXEC permissions on the procedures you want them
to be able to execute. If you only want them to be able to see the code on
some procedures, you can grant then VIEW DEFINITION.

Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:
SQL 2005:
SQL 2000:


Relevant Pages

  • Permissions on Dynamic SQL
    ... I have some code where I create some SQL and execute it with an ADO ... permission to directly update). ... but it doesn't work to just add that clause to the dynamic SQL ... Is there a way I can allow a user to execute a dynamically created SQL ...
  • Re: executing a process from SQL Server
    ... I actually dont want to execute it from SQL Server.We already have another ... instead of stored procs use java. ... I want to write this process in java instead of bunch of stored ... Why do you want to execute it from SQL Server? ...
  • Minimum Permissions for sp_adduser
    ... I am trying to create a new user who has the permission to create a new user ... I have greated him only the rights for excuting sp_adduser. ... What are the minimum set of privilleges required for a user to execute ... I am talking only about SQL 2000 here. ...
  • Re: Sql / Dot Net General Discussion
    ... My primary experience is developing applications using VB or DotNet. ... have some sql skills but they are limited. ... We had very limited DTS's wrtten and stored procs were ... apps that call other stored procs, that call others, etc. ...
  • Re: Opinions on approach, please...
    ... Create a data layer program per table, ... number of bind variable tokens in your dynamic SQL. ... then simply EXECUTE that without passing it any parameters? ... Hard code WHERE clauses. ...