May 9, 2003 at 4:10 pm
Hi there, I want my user to be able to run xp_cmdshell, but it seems that only sysadmins can run this command? Here is what books online says. Anyone know how a user can run xp_cmdshell without being an administrator?
"Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users."
May 9, 2003 at 4:16 pm
You could write a wrapper stored procedure that calls xp_cmdshell and grant your users writes to execute the wrapper.
May 9, 2003 at 4:32 pm
I am not sure what a wrapper sp is.
The xp_cmdshell is in a stored procedure that is being called from a parent sp. But when he runs it he gets an error.
Thanks.
May 9, 2003 at 4:49 pm
I think if you create a stored procedure named abc that calls xp_cmdshell and grant EXECUTE abc rights to the given user, the user should be able to execute abc even if he cannot execute xp_cmdshell directly.
May 9, 2003 at 5:55 pm
Hi mr, what you are suggesting is the way things are already set up. The problem is, I have another user with the same permissions and he is able to run the stored procedures just fine. Any other ideas?
May 9, 2003 at 6:05 pm
So you have two users with exactly the same EXECUTE permissions to a stored procedure. One user can run the proc, another one cannot. Isn't it a strange condition? Try to drop your unfortunate user and recreate it again with all the appropriate permissions. Also, to ensure pemissions are always granted properly and consistently, do it through script only, not through the Enterprise Manager.
Good luck.
May 12, 2003 at 9:43 pm
If it is SQL 2000, you have to setup Proxy account for non sysadmins to run xp_cmdshell.
May 15, 2003 at 12:43 am
Usually some dba don't want do give permissions for xp_cmdshell ( very powerful ). So if you want to manipulate files ( create, read ,etc ) try use sp_OA's and create FileSystem object ( make a search on msdn ) , using only permissions for these SP's.
If this is not your case sriramv just said the solution .
Hope that helps
May 15, 2003 at 6:59 am
One of the tools that I wrote has an xp_cmdshell wrapper inside, you can check it out if you want - http://stripes.docmusic.com
Hope this helps.
May 16, 2003 at 2:57 am
quote:
I think if you create a stored procedure named abc that calls xp_cmdshell and grant EXECUTE abc rights to the given user, the user should be able to execute abc even if he cannot execute xp_cmdshell directly.
BOL:
quote:
EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure.
I would interpret shahgols problem with two different users having EXECUTE permission on the xp_cmdshell wrapper SP but just one gets permission violation as a possible problem with dynamic SQL. Could shahgols be launching xp_cmdshell inside a dynamic SQL statement and only one of the users belongs to the sysadmin role? That seems to be the most probable reason they get different SP EXECUTE results...
//Hanslindgren
May 16, 2003 at 3:28 am
I've read again all the posts and I think there's no way to wrap stored procedures so you can run a SP that you don't have permissions . If so, why there are permissions ? Usually we use that SP's inside other user defined ones right?
hanslindgren had focused one point you sould check: Server Roles , Database Roles, and object permissions.
Also, If you are runing sp under a job , the user who runs it could be diferent .
LM
May 19, 2003 at 1:26 pm
As Sriramv said, You need to setup a proxy acct using xp_sqlagent_proxy_account to garnt the access
Edited by - shastryv on 05/19/2003 1:46:55 PM
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply