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

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

Date: Tue, 6 Jan 2004 09:24:45 +0100

Sorry, you are correct, I misread the original message. Unfortunately I
don't think it is possible to acheive what you want to achieve. I guess you
should take care who can create the procedures, so you can trust the person,
if you want this person to use the dbo user. 

-- 
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Peter Daniels" <nospampedro@yahoo.com> wrote in message
news:2fd8f155.0401051432.6deec0fb@posting.google.com...
> Thank you for the reposnse, but I think you missed the question.  Your
> reponse is angled towards data access permissions.  My question is
> about object creation permissions.  I want a devloper to be able to
> CREATE and ALTER stored procedures in the dbo schema, but not be able
> to CREATE, ALTER, or DROP tables or other objects.
>
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@avtenta.si> wrote in
message news:<ua9a#ud0DHA.2324@TK2MSFTNGP09.phx.gbl>...
> > 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

  • Problem is w/ .ADP..Re: SQL db Permissions for users not working
    ... You do not have SELECT permissions on the ... SysObjects system table in the database. ... figured out that qualifying the database owner (dbo in my ... >> I feel that the object owner is not dbo, ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... If 'select user' is returning DBO, it means you are connecting AS DBO, and ... testing the permissions the new user has. ... Are you saying that the permissions to create the stored procedure are ... Dan D. ...
    (microsoft.public.sqlserver.security)
  • Re: How to prevent DELETEs in a table
    ... What you say about dbo is true, but it is even MORE true about someone who ... is in the sysadmin role. ... No permissions are ever checked for someone in the ... the deny is not applied. ...
    (microsoft.public.sqlserver.server)
  • Re: How to prevent DELETEs in a table
    ... It is the dbo database USER, not server-level groups, that determins ... It has implicit permissions that can not be denied. ... SQL Server just skips any permission validation for sysadmins. ...
    (microsoft.public.sqlserver.server)
  • Re: Control over creation of procs & views owned by dbo
    ... To add on to Jasper's response, you could also change object ownership to ... 'dbo' with sp_changeobjectowner. ... security context of the invoking user, not the object owner. ... need permissions on only directly referenced objects. ...
    (microsoft.public.sqlserver.security)