Re: DDL Permissions - CREATE PROCEDURE, but no CREATE TABLE

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka_at_avtenta.si)
Date: 01/03/04


Date: Sat, 3 Jan 2004 10:26:19 +0100

Have you tried to use only the REFERENCES permission on the table for the
user creationg the SP? Check the "Owners and Permissions" topic in Books
OnLine
(mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\ar
chitec.chm::/8_ar_da_2s4z.htm).
HTH,

-- 
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@yahoo.com> wrote in message
news:2fd8f155.0401021018.12de3b15@posting.google.com...
> Env: SQL Server 2000 SP3a on Windows 2k Server SP4
>
> I want to allow devlopers to create and alter sprocs in the dbo
> schema, but not create or alter tables (in any schema).  I tried:
>
> GRANT CREATE PROCEDURE TO <user>
>
> ...but it will only allow the user to create procedures within their
> own user schema - not in the dbo schema.  "Server: Msg 2760, Level 16,
> State 1, Procedure testspo3, Line 2 Specified owner name 'dbo' either
> does not exist or you do not have permission to use it."
>
> So then I tried adding the user to the ddl_admin fixed db role and
> then executing:
>
> DENY CREATE TABLE TO <user>
>
> ...I thought I was OK at first.  The user could create dbo owned
> sprocs, alter them, and not create tables in any schema.  BUT, they
> can DROP TABLE!  Of course, you can't DENY DROP <object>.
>
> Any idears?
>
> TIA,
>
> -Peter