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

Relevant Pages

  • Re: Yukon schemas
    ... ALTER on a schema is a very powerful permission and should not be granted ... Although ALTER on the schema will not allow the creation of new tables, ... is owned by dbo and granting ALTER on it will allow the grantees to create ...
    (microsoft.public.sqlserver.security)
  • Re: Grant Create View Ability Without Over Permissioning
    ... All objects are part of the dbo schema. ... permission to use it. ... Thanks for the idea Kevin. ... full ALTER rights on the schema seems a bit much. ...
    (microsoft.public.sqlserver.server)
  • Re: Schema Users and permissions
    ... the user has to have BOTH permission to CREATE TABLE and permission to ... ALTER SCHEMA in the schema where the tables are to be created. ... CREATE TABLE is a separate privilege, outside of the ability to ALTER any ...
    (microsoft.public.sqlserver.security)
  • Re: Schema Users and permissions
    ... CREATE TABLE is a separate privilege, outside of the ability to ALTER any ... Once someone has CREATE TABLE permission, they need permission to ALTER ... whatever schema they are going to create a table in. ...
    (microsoft.public.sqlserver.security)
  • Re: Schema Users and permissions
    ... CREATE TABLE is a separate privilege, outside of the ability to ALTER any ... Once someone has CREATE TABLE permission, they need permission to ALTER ... whatever schema they are going to create a table in. ...
    (microsoft.public.sqlserver.security)