August 29, 2002 at 9:49 am
I am trying to run XP_CmdShell from a user that I do not want to have system administrator server role. This command does not seem to work unless the user is has this role.
Is there a way to change the permissions of a role/user (probably a secret SQL command).
Thanks
August 29, 2002 at 11:28 am
You can use a GRANT EXECUTE, so far as I am aware. However, you need to have the proxy account setup. Any users trying to execute xp_cmdshell who are not sysadmins will do so under the user context specified for the proxy account.
Also, the warning from BOL:
quote:
Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 30, 2002 at 4:18 am
Hi, the grant execute does not seem to work for this scenario.
I have tried a few other things but yet no success...
I have tried
1)adding the domain user that is sending the command to the administrators of the server.
2)making the user the DBO of the db that it will be running from
3)adding the user in the permissions of the extended stored procedure
I do not understand about the proxy account. We have never used them in our business.
Any other ideas?? Thanks
August 30, 2002 at 9:42 am
On the SQLAgent proerties box, there is the place for a proxy account, which will be the account under which xp_cmdshell runs.
don't forget xp_cmdshell is in master, not another db, so the user needs rights to run it in master.
Steve Jones
September 2, 2002 at 3:55 am
Thanks
However, when I enter a user I receive the message "Error executing stored procedure: Specified user cannot log in"
I have tried with various users with differnt sorts of access (domain users, local users, sysadmin user, access to master)
Is there anything that needs to be set up on the user to allow it to be the proxy user?
September 2, 2002 at 6:35 am
I have had time to dig around on the net today and have found the answer, thanks for the help.
This is the "fix" (thanks to Microsoft support):
In Security Settings/Local Policies/User Rights Assignment make sure the account you want to use as your proxy has these privileges:
Act as part of the operating system
Increase quotas
Log on as a service
Replace a process level token
Log on as a batch job
After setting those and a reboot
September 2, 2002 at 9:13 am
Also there is a little bug in S2K sp1 (fixed in sp2) where you need to give the proxy account access to the sql temporary directories
Steven
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply