Re: Controlling create & drop proc, view privilege

From: Mani (Mani_at_discussions.microsoft.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 08:33:03 -0800

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:
>
> >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: Controlling create & drop proc, view privilege
    ... A user needs to be a member of db_owner or db_ddladmin ... roles to create a objects owned by dbo. ... It depends first on ownership the ownership chain. ... 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: 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: changing table owner name- sp_changemergearticle
    ... But when I insert the result set of all the user table names and owner ... I have a db with a few tables which has owner than dbo. ... In order to avoid this i went to the article properties on the ... incorrect syntax when i execute it.? ...
    (microsoft.public.sqlserver.replication)
  • 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)