May 12, 2009 at 7:32 am
I have a user who needs to execute a stored procedure that exports data to a .csv file via bcp.
Since he is not a member of the sysadmin group, I created the ##xp_cmdshell_proxy_account## proxy via sp_xp_cmdshell_proxy_account, and assigned that credential to a known good sysadmin group member account (i.e. mine). Even when I've created the proxy, the user still gets the following error:
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
From the tweaking that I've done on our test server, the only way that I've been able to find that actually allows the user to run xp_cmpshell is to give him the sysadmin role, which is not the way to go. Does anyone have any suggestions on why the proxy isn't allowing hime to run?
Thanks!
May 12, 2009 at 8:00 am
Another way that might work is to create the stored procedure using the WITH EXECUTE AS clause. By specifying the context in which the procedure is executed, you can control which user account the SQL Server 2005 Database Engine uses to validate permissions on objects that are referenced by the procedure. For example:
CREATE PROCEDURE dbo.usp_cmdshell
WITH EXECUTE AS 'sa'
AS
BEGIN
exec xp_cmdshell 'dir c:\'
END
GO
May 12, 2009 at 9:03 am
I found that by creating a role explicitly under the master database, rather than at the server level, that had execute assigned on xp_cmdshell, and assigning my user to the role, that he was able to successfully run the procedure.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply