Stored Procedure Permissions

[hydrorocks]

Hi,

I don't know if it is possible, but I need to give certain Users the right to add or drop other users to roles. I would need a procedure which will simplified look like this:

CREATE PROCEDURE dbo.add
AS
EXEC sp_addrolemember 'role', 'user'
GO

The Problem is, that no User can execute this procedure, even if he has the permission for this procedure because for sp_addrolemember you need the db_owner role. I don't want to give all user the db_owner role, only users with the role for this procedure should be allowed to use it.

My Question: Can I create a procedure, which grants db_owner rights for a user only for this one procedure. Like say, everybody who is allowed to use this procedure is allowed to use this procedure and only this procedure as db_owner?

Any help would be appreciated, and sorry for my english... )

Hydro

 

[sweth]

Best way for all Sql server backend application is create stored procedure and from front create an user to which has only to connect sql server database and to execute stored procedure. This works fine except for dynamic sql in stored procedure.

Grant execute on <spname> to <username>

 

[hydrorocks]

Hi,

Thanks for your fast answer, but it doesn't solve my problem I think...

I have a procedure, and grant execute to a user, but ->in<- this procedure I use another procedure, a system procedure (sp_addrolemember) and I can't grant a user rights for that one. Hey can execute the procedurte, but when it comes to the system procedure he need's more rights than he has.

And I would like to give it only for the one procedure. Like execute myProcedure as db_owner...

 

[NoCoolHandle]

Who owns the original proc?
is it dbo?