2

I am creating a group for users who will need to be able to create and delete procs but also grant execute permissions to other users.

GRANT CREATE PROCEDURE TO [xxx\xxx]
GRANT ALTER ON SCHEMA :: dbo TO [xxx\xxx]

However what permissions to I need to grant the group such that they can grant execute permissions to an object?

Thanks

Tom
  • 144
  • 8
  • [This might help](http://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username) it's a thread about granting various permissions. You may just have to grant these people ALL privileges – tkendrick20 Jul 30 '13 at 15:52
  • 3
    @tkendrick20 the accepted answer there shows how to grant certain permissions (but not execute, or the ability to grant execute); the other answers there all seem to imply that the "solution" is to put the user in the db_owner role. – Aaron Bertrand Jul 30 '13 at 16:18
  • Would that not be a solution? – tkendrick20 Jul 30 '13 at 18:18
  • @tkendrick20 it's "a" solution, sure. But it might be giving more power than intended and needed. Why not just make them sysadmin? That would work too. – Aaron Bertrand Jul 30 '13 at 18:21
  • 1
    Cool, that makes 2 solutions – tkendrick20 Jul 30 '13 at 18:23
  • 2
    @tkendrick20 It's a solution in the same way that removing all locks from your home is a "solution" to giving a guest access. – JNK Jul 30 '13 at 18:24

1 Answers1

7

According to the documentation, this is supposed to work:

GRANT EXECUTE ON SCHEMA::dbo TO [xxx\xxx] WITH GRANT OPTION;

However in my minimal testing it required:

GRANT CONTROL ON SCHEMA::dbo TO [xxx\xxx];

I strongly recommend you don't follow the advice in the comments, which suggest it might be a good idea to "grant ALL permissions" or use db_owner or sysadmin. Granting someone more permissions than they need is just a lazy way to ensure you will get fired later. I recommend anyone advocating this blind giving of keys to the castle to review the principle of least privilege.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490