Can't activate xp_cmdshell for one session only

Question

I tried activating xp_cmdshell just before using it in my nightly SP (running on a SQL job) and then turning it off, but it didn't work. The history (image below) seems to imply that the RECONFIGURE keyword isn't working.

CREATE PROCEDURE [dbo].[NightlySP]

AS
BEGIN

--Allow xp_cmdshell to be used for the duration of this session only
-- To allow advanced options to be changed.  
EXECUTE sp_configure 'show advanced options', 1;  

-- To update the currently configured value for advanced options.  
RECONFIGURE;  

-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 1;  

-- To update the currently configured value for this feature.  
RECONFIGURE; 

/*
...rest of SP where I use xp_cmdshell...
*/    

--Turn off xp_cmdshell
-- To allow advanced options to be changed.  
EXECUTE sp_configure 'show advanced options', 1;  

-- To update the currently configured value for advanced options.  
RECONFIGURE;  

-- To enable the feature.  
EXECUTE sp_configure 'xp_cmdshell', 0;  

-- To update the currently configured value for this feature.  
RECONFIGURE; 

END

Answer 1

To run xp_cmdshell with least-privileges configure an xp_cmdshell Proxy Account, specifying a Windows identity with appropriate privileges, and grant execute on xp_cmdshell to the account that runs the job.

Otherwise, the identity running the script must be a sysadmin, and xp_cmdshell runs the OS commands as the SQL Server Service Account.

The SQL Server service account shouldn't be highly privileged, but it always has file-level access to all databases and backups. Attached databases are protected from OS-level access but backups and detached databases, are vulnerable. And the Service account can accumulate privileges like the privilege to view uninitialized free space on local disks (aka "perform volume maintenance tasks" required for instant file initialization), or network permissions for file shares.

So either leave it on, and run the job as a sysadmin, knowing the risks, or provision a lower-privilege Windows user to run the job. You can use a single local Windows account for both the job owner and the xp_cmdshell proxy account.