Accessing stored procedure with another role?

Hi NG,
I have the problem that I need to give certain users the right to add and
remove other users from roles. I have a stored procedure that looks
simplified like this
CREATE PROCEDURE UpdateUserRoles
(
@.UserName varchar(255),
@.RoleName varchar(255),
)
AS
EXEC sp_addrolemember @.RoleName, @.UserName
GO
This works for me fine because I have the db_owner role, but if you don't
have the role you are not allowed to execute the system sp
"sp_addrolemember". And I need that some users of the programm can execute
this procedure, but I can grant them all rights there are, they habe to be
in the db_owner role and I don't want to do that for obvious reasons.
My question, is it possible, that everybody who ist allowed to use this
procedure is allowed to use it as db_owner? I don't want them to be in the
db_owner role, they should just have the right for this one procedure...
I tried:
GRANT Execute ON UpdateUserRoles to User as db_owner
but I got
Grantor does not have GRANT permission...
Any help is appreciated...Christian
I've got
Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49
User does not have permission to perform this action.
I'm affraid you cannot. However , please take a look at an Application Role
that you can activate on the connection to the database
"Christian" <uce@.cash4banners.de> wrote in message
news:u6gw5jLnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> I have the problem that I need to give certain users the right to add and
> remove other users from roles. I have a stored procedure that looks
> simplified like this
> CREATE PROCEDURE UpdateUserRoles
> (
> @.UserName varchar(255),
> @.RoleName varchar(255),
> )
> AS
> EXEC sp_addrolemember @.RoleName, @.UserName
> GO
> This works for me fine because I have the db_owner role, but if you don't
> have the role you are not allowed to execute the system sp
> "sp_addrolemember". And I need that some users of the programm can execute
> this procedure, but I can grant them all rights there are, they habe to be
> in the db_owner role and I don't want to do that for obvious reasons.
> My question, is it possible, that everybody who ist allowed to use this
> procedure is allowed to use it as db_owner? I don't want them to be in the
> db_owner role, they should just have the right for this one procedure...
> I tried:
> GRANT Execute ON UpdateUserRoles to User as db_owner
> but I got
> Grantor does not have GRANT permission...
> Any help is appreciated...
>|||Thanks Uri.
Changing shortly the connection to another user worked for me. Thanks
again...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uZ2KtuLnFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Christian
> I've got
> Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49
> User does not have permission to perform this action.
>
> I'm affraid you cannot. However , please take a look at an Application
> Role that you can activate on the connection to the database
>
>
> "Christian" <uce@.cash4banners.de> wrote in message
> news:u6gw5jLnFHA.3656@.TK2MSFTNGP09.phx.gbl...
>