Re: Granting GRANT permissions

From: Josh N. (JoshN_at_discussions.microsoft.com)
Date: 09/13/05

  • Next message: M D: "not a trusted connection" 
    Date: Tue, 13 Sep 2005 14:41:23 -0700

    Yes I want users who are not in db_owner or db_ddladmin to be able to create
    procedures for dbo. But it appears my initial assumption about this is true,
    which is unfortunate.

    I appereciate everyone's responses and thank you. Unless anyone knows of a
    way to allow users to do this without giving them db_ddladmin or db_owner, I
    apparently am forced to leave a database wide open to people I don't trust
    (this wasn't my decision...)

    Josh

    "Sue Hoegemeier" wrote:

    > 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: M D: "not a trusted connection"

    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: Granting GRANT permissions
      ... not DBO, but you can act as the owner of the object. ... > that errors out saying you can't use AS when granting those rights. ... >> Creators of stored procedures (standard users with CREATE PROCEDURE ... >> procedure and then grant permissions on it to another user B ...
      (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)