Control over creation of procs & views owned by dbo

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
ManiThey 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|||Thanks Jasper.
"Jasper Smith" wrote:

> They would have to be a member of the db_ddladmin or db_owner fixed databa
se
> 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 i
n
> 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...
>
>|||To add on to Jasper's response, you could also change object ownership to
'dbo' with sp_changeobjectowner.
Regarding the second part of your question, stored procedures run in the
security context of the invoking user, not the object owner. Due to
ownership chains, permissions on indirectly referenced objects are not
checked as long as the objects involved have the same owner. Users only
need permissions on only directly referenced objects.
Note that ownership chains apply only to object permissions, not statement
permissions like CREATE. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"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