Re: Granting GRANT permissions

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 09/13/05

  • Next message: Sue Hoegemeier: "Re: Granting GRANT permissions" 
    Date: Mon, 12 Sep 2005 15:47:02 -0700

     
    Can you elaborate on exactly what you want to do? You can create a table
    owned by dbo if you are in the db_owner role. In that case your user name is
    not DBO, but you can act as the owner of the object.

    There is no way to create a proc owned by dbo without being dbo or being in
    the db_owner role. 

    -- 
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Josh N." <JoshN@discussions.microsoft.com> wrote in message 
news:FCB0EC5B-B880-4B97-A82C-7EA14DF54096@microsoft.com...
> 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: Sue Hoegemeier: "Re: Granting GRANT permissions"

    Relevant Pages

    • Re: Granting GRANT permissions
      ... How do I allow a user to create a stored procedure for 'dbo' without giving ... that errors out saying you can't use AS when granting those rights. ... > can grant permissions on their own procedures to other users. ...
      (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)
    • Re: Minimum Permissions for sp_adduser
      ... It turns out that db_owner will not do, the account must actually be the owner of the database and therefore the 'dbo' user. ... "sp_adduser" is hardcoded to reject even these rights. ... But if you change to the SQL Server 2000 preferred command, "sp_grantdbaccess" it will work for you. ... But still i am not able to execute sp_adduser using this new user. ...
      (microsoft.public.sqlserver.security)
    • User rights problem
      ... "Specified owner name 'dbo' either does not exist or you do not have ... rights which i should give to the "developer" login for donig so. ...
      (microsoft.public.sqlserver.security)
    • Re: Setting Table Permissions?
      ... > Empty - Revoked ... > By default a user has no rights to an object unless they are dbo or in a ... > role that has rights. ... >> everythign with an X where I don't want user access, ...
      (microsoft.public.sqlserver.security)