SQL Server Performance Forum – Threads Archive
wrapper sp for sp_addrolemember
I want to write a stored procedure that can be executed by anyone. The stored procedure will perform some checks on the user executing the procedure and if the checks are OK, it will execute the sp_addrolemember stored procedure and add a role to the user in question. This wrapper stored procedure resides in a database where the owner of the database is assigned the sysadmin role. The stored procedure is owned by the dbo. I have granted execute privilege on this wrapper stored procedure to public. When I test it and log in as a normal user with no special privileges, the procedure fails saying that Msg 15247, Level 16, State 1, Server JEFF, Procedure sp_addrolemember, Line 49User does not have permission to perform this action. The owner of the stored procedure have the privilege to execute sp_addrolemember and I thought that if execute privilege on the stored procedure is granted to public (or a particular user), the user executing the procedure will not need direct privileges to perform all the tasks within the stored procedure. Am I wrong or is something missing. Thank You
You would have to make the user a sysadmin or security admin. I would very strongly recommend against both. Create a table called permission_queue. Give them execution on a proc that adds an entry to the table. Have a schedule job run ever 5 seconds or so that runs the necessary permissions and runs as sa. Never give users server level permissions when you can avoid it (which should be 100%). MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Derrick, Thanks for the comment. I was trying to stay away from having to run a process every so often in order to achieve what I need. The stored procedure I want checks against a table that indicates if the user executing the procedure should be granted the role or not. I am under the impression that if the owner of the stored procedure has all the privileges needed to perform the tasks within the procedure, then the execute permission on the procedure can be granted to anyone and they would not need permissions need to perform tasks with the stored procedure. I thought this was a basic concept within SQL Server that allow users to perform certain tasks via a stored procedure without actually having to be granted all the underlying privileges required to perform the tasks inside the stored procedure. Am I completely off base on this understanding. Thank You,
]]>