Re: Control over creation of procs & views owned by dbo
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/28/04
- Next message: Uri Dimant: "Re: Controlling create & drop proc, view privilege"
- Previous message: Mani: "Re: Control over creation of procs & views owned by dbo"
- In reply to: Mani: "Control over creation of procs & views owned by dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 27 Oct 2004 19:02:25 -0500
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
- Next message: Uri Dimant: "Re: Controlling create & drop proc, view privilege"
- Previous message: Mani: "Re: Control over creation of procs & views owned by dbo"
- In reply to: Mani: "Control over creation of procs & views owned by dbo"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
