Posted by Erland Sommarskog on 09/20/07 21:15

Matthew Wells (Matthew.Wells@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@sommarskog.se

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

• Next in forum: Re: number of databases in sql server 2005
• Prev in forum: Re: How Do I give EXECUTE Permissions on Stored Procedures?
• Thread view: Re: Create Function permission...

[Reply to this message]