MS SQL 2000 : How to make the command xp_cmdshell to be executed by a non-sysadmin user?
Hi,
I have a stored procedure that need to execute a batch file which located on the SQL server folder. I do find a very useful command which can do this : master..xp_cmdshell. However, no matter how I create a user, it still need the user which execute this command to be sysadmin. However, according to the MS SQL online book, it says :
=-=-=-=-=-=-=-=-=-=-=-=-=-
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
=-=-=-=-=-=-=-=-=-=-=-=-=-
therefore, I think there should be a way to config a user which no need to be sysadmin role but still can execute master..xp_cmdshell as it says "...but can be granted to other users..."
Can anyone help ?
Thanks
Stanley
I have a stored procedure that need to execute a batch file which located on the SQL server folder. I do find a very useful command which can do this : master..xp_cmdshell. However, no matter how I create a user, it still need the user which execute this command to be sysadmin. However, according to the MS SQL online book, it says :
=-=-=-=-=-=-=-=-=-=-=-=-=-
Permissions
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
=-=-=-=-=-=-=-=-=-=-=-=-=-
therefore, I think there should be a way to config a user which no need to be sysadmin role but still can execute master..xp_cmdshell as it says "...but can be granted to other users..."
Can anyone help ?
Thanks
Stanley
imitchie
you need to use the GRANT command. search for GRANT in SQL books online.
grant execute on xp_cmdshell to alogin
from the master db (no quotes, where alogin is a valid login)
from the master db (no quotes, where alogin is a valid login)
ASKER
Thanks imitchie,
I have tried, but still failed. What I have done are as follow.
I login as "sa" in Query analyser and execute --> grant execute on xp_cmdshell to ABC <-- where ABC is a native SQL user (without any administrator role) which created with deafault DB to "master" and with public authority. (I do try to make user ABC with db_owner of master DB but still fail)
I login "ABC" in Query analyser and execute ---> exec xp_cmdshell 'dir' <--- and the following error returned.
=-=-=-=-=-=-=-=-=-=-=-=-=-
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1813 from GetProxyAccount on line 604
=-=-=-=-=-=-=-=-=-=-=-=-=-
Kindly please advise what's wrong/missing with my procedure ... thanks
Cheers
Stanley
I have tried, but still failed. What I have done are as follow.
I login as "sa" in Query analyser and execute --> grant execute on xp_cmdshell to ABC <-- where ABC is a native SQL user (without any administrator role) which created with deafault DB to "master" and with public authority. (I do try to make user ABC with db_owner of master DB but still fail)
I login "ABC" in Query analyser and execute ---> exec xp_cmdshell 'dir' <--- and the following error returned.
=-=-=-=-=-=-=-=-=-=-=-=-=-
Msg 50001, Level 1, State 50001
xpsql.cpp: Error 1813 from GetProxyAccount on line 604
=-=-=-=-=-=-=-=-=-=-=-=-=-
Kindly please advise what's wrong/missing with my procedure ... thanks
Cheers
Stanley
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thx a lot. ^_^