Re: Granting GRANT permissions

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

Date: Mon, 12 Sep 2005 13:45:02 -0700

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

Relevant Pages

  • 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: I still dont get it.
    ... > You can create a stored procedure to call xp_cmdshell to run a command. ... > though the originator has no such rights. ... > Russell Fields ... >> A has rights to sysadmin sproc, if I pass in the table to be created as ...
    (microsoft.public.sqlserver.security)
  • Re: Granting EXEC for read only SPs
    ... clearly understanding which stored procedures the user should have rights to ... problem is that the stored procedure is running under its own rights ... I created database role 'X' and granted EXEC permission for all SPs. ... the ones that modify data. ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 2005 Stored Procedure security annoyances
    ... Can you grant an EXECUTE permission on stored procedure? ... I have came to the maturity level to stop using the sa account when I ... I mean it is unrealistic to have to manually set for each SP the rights ...
    (microsoft.public.sqlserver.security)
  • Re: Granting EXEC for read only SPs
    ... problem is that the stored procedure is running under its own rights ... a stored procedure is doing before granting rights to it. ... I created database role 'X' and granted EXEC permission for all SPs. ...
    (microsoft.public.sqlserver.security)