Controlling create & drop proc, view privilege

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 applicatio
n
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
ManiMani
You can EXECUTION permission on the stored procedure for the user
Also ,you can remove him/her from sysadmin fixed server role but he/she
should be member db_owner fixed database and must qualified User.sp
"Mani" <Mani@.discussions.microsoft.com> wrote in message
news:0C3FBF63-843E-465E-98C0-4BE9152BF08F@.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|||1. A user needs to be a member of db_owner or db_ddladmin
roles (or sysadmin) to create a objects owned by dbo.
Members of db_owner and db_ddladmin need to qualify the
owner as dbo.object when they create the objects to be owned
by dbo.
2. It depends first on ownership the ownership chain. If the
ownership chains are intact, the secuirty is checked for
permissions to execute the stored procedure only. If the
ownership chain is broken, permissions are checked on each
branch where the owner of the object is different. You can
find more information in books online under ownership chains
-Sue
On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
<Mani@.discussions.microsoft.com> wrote:

>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 applicati
on
>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 woul
d
>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|||Thanks Uri and Sue for your responses.
"Sue Hoegemeier" wrote:

> 1. A user needs to be a member of db_owner or db_ddladmin
> roles (or sysadmin) to create a objects owned by dbo.
> Members of db_owner and db_ddladmin need to qualify the
> owner as dbo.object when they create the objects to be owned
> by dbo.
> 2. It depends first on ownership the ownership chain. If the
> ownership chains are intact, the secuirty is checked for
> permissions to execute the stored procedure only. If the
> ownership chain is broken, permissions are checked on each
> branch where the owner of the object is different. You can
> find more information in books online under ownership chains
> -Sue
>
> On Wed, 27 Oct 2004 14:33:04 -0700, "Mani"
> <Mani@.discussions.microsoft.com> wrote:
>
>