Granting GRANT permissions

Granting GRANT permissions

Post by Sm9zaCBOL » Sun, 11 Sep 2005 05:41:02


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
 
 
 

Granting GRANT permissions

Post by Kalen Dela » Sun, 11 Sep 2005 06:09:20


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

 
 
 

Granting GRANT permissions

Post by Jasper Smi » Sun, 11 Sep 2005 06:18:20

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.yqcomputer.com/
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.yqcomputer.com/
 
 
 

Granting GRANT permissions

Post by Sm9zaCBOL » Wed, 14 Sep 2005 05:45:02

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
 
 
 

Granting GRANT permissions

Post by Kalen Dela » Wed, 14 Sep 2005 07:47:02


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
 
 
 

Granting GRANT permissions

Post by Sue Hoegem » Wed, 14 Sep 2005 09:19:50

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

Granting GRANT permissions

Post by Sm9zaCBOL » Thu, 15 Sep 2005 06:41:23

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