June 17, 2009 at 3:31 am
I'm trying to run a defrag from xp_cmdshell but I get 'You must have Administrator privileges to defragment a volume.' when I run it, it's running using the account that runs the services that has full admin access to the server, when I run 'whoami' from xp_cmdshell it tells me I am this service user, the user has full admin access to the server.
If I run the defrag from the server as this user it works fine. Is there anything I need to check that might get this to run, I don't really want to hardcode a password in my xp_cmdshell to run the command as a certain user.
Any ideas?
Thanks
June 17, 2009 at 6:45 am
I've sorted the problem, I found on a microsoft forum that someone had a similar problem, they had no idea why it worked but they rebooted and it worked.
I rebooted the server and it works, I have no idea why, I didn't change anything on the server!!!!!
June 18, 2009 at 4:10 am
The right you need to do a defrag is: "Perform volume maintenance tasks" also known as SeManageVolumePrivilege.
Accounts in the local Administrators group have this right by default, but you can grant it to any account.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
June 18, 2009 at 4:24 am
The account I was using is in the administrators group on the server and I've just checked and that group has the rights you mentioned.
The question is why would running a defrag from the server work but it wouldn't work using the same user running the defrag from xp_cmdshell until the server was rebooted even though nothing had been done on the server security wise or anything else in fact, it almost seems as if xp_cmdshell had to register itself and then need a reboot before working correctly. (it was also the first time xp_cmdshell had ever been run on this server).
I don't know if running :
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
and
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
Go
at the end of the script would have any weight on the matter, I wouldn't have thought so, you should be able to change these options without a reboot, that's why you have RECONFIGURE.
Any comments welcome, thanks.
June 18, 2009 at 4:29 am
Just a guess... It may be something to do with how Windows recognises when a newly used account gets local admin rights.
I know that Windows build a list of local Admin uswers at boot time, and I have found that adding a user to local Administrators tends to need a reboot before the rights are recognised.
Why this should happen with xp_cmdshell when it is running with the SQL service account rights is beyond me. Maybe a Windows expert will know.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply