Granting GRANT permissions

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 right
s
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 table
s.
Thanks
JoshCreators 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
>|||The owner of a stored procedure automatically has the ability to grant
others the right to execute it. They do not need to be in any special role.
HTH
Kalen Delaney
www.solidqualitylearning.com
"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
>|||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 publ
ic
> 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...
>
>|||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:
>
>|||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:
[vbcol=seagreen]
>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:
>|||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:
>
>