Re: Granting GRANT permissions

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 09/13/05

  • Next message: john.sinclair_at_gmail.com: "Domain user cannot connect with integrated security" 
    Date: Mon, 12 Sep 2005 18:19:50 -0600

    I'm not really following what you mean either. I'm guessing
    that you want a user to be able to create a stored procedure
    that is owned by dbo without the user being a member of
    db_owners. You can add the user to the db_ddladmin role and
    they can create stored procedures owned by dbo. When they
    create the stored procedures, they need to qualify them as
    being owned by dbo....for example
    create procedure dbo.SomeStoredProcedure <etc>
    Members of db_ddladmin can also edit the stored procedures.
    However, they inherit a lot of other permissions in the
    process as they can add, modify, drop database objects, not
    just stored procedures.
    More info on exactly what you want to do would be good as it
    is not necessarily a good thing to give these rights to
    users.

    -Sue

    On Mon, 12 Sep 2005 13:45:02 -0700, "Josh N."
    <JoshN@discussions.microsoft.com> wrote:

    >Yes this is what I was refering to. Thank you for your answer but I now
    >realize I have a much larger problem.
    >
    >How do I allow a user to create a stored procedure for 'dbo' without giving
    >them owner rights??? I tried to "grant create procedure to xxx as dbo" but
    >that errors out saying you can't use AS when granting those rights.
    >
    >If anyone knows of a way to allow a user to create procedures and edit them
    >for dbo without being dbo I would appreciate your response.
    >
    >Thanks
    >Josh
    >
    >
    >
    >
    >"Jasper Smith" wrote:
    >
    >> Creators of stored procedures (standard users with CREATE PROCEDURE rights)
    >> can grant permissions on their own procedures to other users. Is this what
    >> you mean? i.e. if user A has CREATE PROCEDURE rights they can create a
    >> procedure (A.P1) and then grant permissions on it to another user B (grant
    >> exec on A.P1 to B). This is without them being in any other role than public
    >> in the database.
    >>
    >> --
    >> 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
    >>
    >> "Josh N." <Josh N.@discussions.microsoft.com> wrote in message
    >> news:6BA4EBA0-9BC5-4A84-BBA8-A18502BFB93E@microsoft.com...
    >> >I have the need to allow users GRANT permissions for their created stored
    >> > procedures. However I do not wish to give these users db_securityadmin
    >> > rights
    >> > in the database they will be creating said stored procedures in.
    >> >
    >> > Is there a way to only give them GRANT EXEC rights and nothing else? I
    >> > really don't like they idea they can modify groups and the users in those
    >> > groups with db_securityadmin rights as well as modify access rights to
    >> > tables.
    >> >
    >> > Thanks
    >> > Josh
    >> >
    >>
    >>
    >>

  • Next message: john.sinclair_at_gmail.com: "Domain user cannot connect with integrated security"

    Relevant Pages

    • Re: Help pls: Not allow users access to tables, stored procedures
      ... tables are owned by dbo. ... SQL Server MVP ... etc) is performed via stored procedures. ... correct permission for select permission on the tables. ...
      (microsoft.public.sqlserver.security)
    • Re: MS Access cant find Recordsource object
      ... object names and stored procedures that reference objects. ... I guess I'll get busy prepending dbo. ... > since later versions are better able to resolve queries where objects ... If I give those users sysadmin privileges, ...
      (microsoft.public.sqlserver.server)
    • Re: dbo. in vs generated code for stored procedures.
      ... all of your stored procedures be owned by dbo so that you don't have ... stored procedures to be owned by dbo, ... If she is not a sysadmin, ... A developer who is a db_owner can ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Preventing Injection - Client Side
      ... > In addition the program needs to read from ... Are they owned by "dbo"? ... To perform DML operations on tables, I would use stored procedures. ...
      (microsoft.public.sqlserver.security)
    • Re: Granting GRANT permissions
      ... procedures for dbo. ... > they can create stored procedures owned by dbo. ... > Members of db_ddladmin can also edit the stored procedures. ... >>that errors out saying you can't use AS when granting those rights. ...
      (microsoft.public.sqlserver.security)