Re: Controlling create & drop proc, view privilege

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 10/28/04 

Date: Thu, 28 Oct 2004 05:52:16 -0600

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 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

Relevant Pages

  • Re: Permission Denied executing SP that reads foreign table!
    ... >> the creators rights and in this case its the dbo who owns the SP. ... > are not checked as long as the objects involved have the same owner. ... All objects across these databases on the same server are ... > the ownership chain, it is the login associated with the object owner. ...
    (microsoft.public.sqlserver.security)
  • Re: Controlling create & drop proc, view privilege
    ... > roles to create a objects owned by dbo. ... It depends first on ownership the ownership chain. ... > permissions to execute the stored procedure only. ... > branch where the owner of the object is different. ...
    (microsoft.public.sqlserver.security)
  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: rda push when owner is not dbo.
    ... If I launch the sql query analyzewr and try to run the ... I'm pretty configdent that the owner name not being qualified is the ... > I pull the table with rda to a local table named task. ... > I also created a test table in the same database with owner dbo and I ...
    (microsoft.public.sqlserver.ce)
  • Re: Question about dropping owners permissions...
    ... When a member of the db_owner role creates an object, ... dbo, must the owner must be specified: ... >>> create databases themselves. ... Can anyone help me reach my end goal: Allowing developers to ...
    (microsoft.public.sqlserver.security)