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

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 10/28/04 

Date: Wed, 27 Oct 2004 23:23:51 +0100

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

Relevant Pages

  • Control over creation of procs & views owned by dbo
    ... to only create & drop stored procs and views in DevDB. ... extra permissions does the user need? ... the user create procs with him as owner. ... that would execute in the owners context and hence would ...
    (microsoft.public.sqlserver.security)
  • Controlling create & drop proc, view privilege
    ... to only create & drop stored procs and views in DevDB. ... extra permissions does the user need? ... the user create procs with him as owner. ... that would execute in the owners context and hence would ...
    (microsoft.public.sqlserver.security)
  • Re: Control over creation of procs & views owned by dbo
    ... > roles to create objects in the dbo schema which would give them too many ... > rights.It's not possible to ... > Jasper Smith ... >> the user create procs with him as owner. ...
    (microsoft.public.sqlserver.security)