Re: Control over creation of procs & views owned by dbo

From: Mani (Mani_at_discussions.microsoft.com)
Date: 10/28/04

  • Next message: Dan Guzman: "Re: Control over creation of procs & views owned by dbo" 
    Date: Wed, 27 Oct 2004 17:01:04 -0700

    Thanks Jasper.

    "Jasper Smith" wrote:

    > They would have to be a member of the db_ddladmin or db_owner fixed database
    > roles to create objects in the dbo schema which would give them too many
    > rights (they would also be able to create tables etc).It's not possible to
    > give them just a subset of the rights if you want them to create objects in
    > the dbo schema
    >
    > --
    > HTH
    >
    > Jasper Smith (SQL Server MVP)
    > http://www.sqldbatips.com
    > I support PASS - the definitive, global
    > community for SQL Server professionals -
    > http://www.sqlpass.org
    >
    > "Mani" <Mani@discussions.microsoft.com> wrote in message
    > news:17DE6BDF-E650-4002-8561-D28836F5F620@microsoft.com...
    > > Hi,
    > >
    > > Is there a way to allow a user, who has access to a db say "DevDB" as
    > > db_datareader, to only create & drop stored procs and views in DevDB. What
    > > extra permissions does the user need ?
    > >
    > > I tried playing with the "grant create proc to user" command. But it lets
    > > the user create procs with him as owner. In the current case, the
    > > application
    > > needs all objects to be owned by dbo, so the user needs to be able to run
    > > "create proc dbo.tempProc as ..."
    > >
    > > In case there is a solution to the above, we might fall into the next
    > > trap.
    > > since the user can create procedures with dbo as the owner, if the SP has
    > > a
    > > drop table command, that would execute in the owners context and hence
    > > would
    > > drop the table. Is that right ? I guess the question is when an SP is
    > > executed does it use the permissions of the owner of the SP or the user
    > > executing the SP
    > >
    > > --
    > > Mani
    >
    >
    >

  • Next message: Dan Guzman: "Re: Control over creation of procs & views owned by dbo"