How do I give a Windows group complete rights (including create) to all
stored procedures and user defined functions without giving them dbo access
in SQL Server 2005? If I have to I can do it from the Management Console,
but I would also like to know the commands.

Thanks

Matthew Wells
Matthew.Wells [at] FirstByte.net

Matthew Wells (Matthew.Wells [at] FirstByte.net) writes:
> How do I give a Windows group complete rights (including create) to all
> stored procedures and user defined functions without giving them dbo
> access in SQL Server 2005? If I have to I can do it from the Management
> Console, but I would also like to know the commands.

GRANT CREATE PROCEDURE TO [Windows\group]
GRANT DROP PROCEDURE TO [Windows\group]
GRANT ALTER ON SCHEMA::dbo TO [Windows\group]

If you use other schemas than dbo, they need ALTER permissions on those
schemas as well.

I'm not sure that you will find ALTER sufficient. In that case, you can
grant CONTROL on the schemas. But as long as they only have ALTER/CONTROL on
schema level, they can create users, certificates etc.


--
Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx