--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works
 SELECT '****************************** Making sure the things we need don''t already exist. ******************************';
    USE MASTER;
   DROP DATABASE MyTester;                 --BE REAL CAREFUL HERE!!! Drops the database I tested against
   EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.
   DROP USER  [yourdomainname\TestDummy];  --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.
   DROP LOGIN [yourdomainname\TestDummy];  --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.
   DROP USER  [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.
   DROP LOGIN [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Recreate my test database and the user which only has "public" privs.
     -- I believe the DEFAULT_SCHEMA is important here.
 SELECT '****************************** Creating [MyTester] DB and TestDummy. ******************************';
 CREATE DATABASE [MyTester];
GO
    USE [MyTester];
 CREATE LOGIN [yourdomainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];
 CREATE USER [yourdomainname\TestDummy] FOR LOGIN [yourdomainname\TestDummy] --This just maps the database for the user
GO
--===== This just displays how limited the TestDummy user is
   EXEC sp_helpuser [yourdomainname\TestDummy];
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Now we build the Login and proxy account using the SqlCmdUser I built in Windows on my box at home.
     -- IMPORTANT!!! A step we cannot skip is that we have to build a user from the SqlCmdUser login.
     -- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!
 SELECT '****************************** Building/Granting Proxy user stuff. ******************************';
    USE [master];
 CREATE LOGIN [yourdomainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
 CREATE USER [yourdomainname\SqlCmdUser] FOR LOGIN [yourdomainname\SqlCmdUser] WITH DEFAULT_SCHEMA=[dbo];
   EXEC sp_xp_cmdshell_proxy_account 'yourdomainname\SqlCmdUser','SqlCmdUser';

--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...
  GRANT EXECUTE ON xp_CmdShell to [yourdomainname\SqlCmdUser];
GO
--===== This just displays how limited even the SqlCmdUser is!!!!
   EXEC sp_helpuser [yourdomainname\SqlCmdUser];
GO
-----------------------------------------------------------------------------------------------------------------------
-- ********** NOTE THAT EVERYTHING ABOVE IS AS WE HAD IT BEFORE! **********
-- ********** NOTE THAT THE ONLY THING WE HAVE TO DO IN THE STORED PROCS (SEE BELOW
-- ********** IS TO INCLUDE "WITH EXECUTE AS OWNER"
-- heh... And Bob's your Uncle!
-----------------------------------------------------------------------------------------------------------------------
--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.
     -- Keep in mind that, right now, we're signed in as a member of "dbo".
    USE [MyTester];
GO
DROP PROCEDURE dbo.GetDirInfo;
GO 
 CREATE PROCEDURE dbo.GetDirInfo
   WITH EXECUTE AS OWNER
     AS
   EXEC xp_cmdshell 'DIR C:\';
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;
;
GO
--===== Give the general puplic privs to run the sproc.
  GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC
;
GO
-----------------------------------------------------------------------------------------------------------------------
--===== Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.
     -- Simulate logging in as a user with low privs...
EXECUTE AS LOGIN = 'yourdomainname\TestDummy'
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

     -- This works... (which is what we want)
  PRINT REPLICATE('=',80);
  PRINT '********** Testing execution of dbo.GetDirInfo **********'
   EXEC dbo.GetDirInfo
SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

     -- This doesn't... (which is also what we want)
  PRINT REPLICATE('=',80);
  PRINT '********** Testing execution of xp_CmdShell directly **********'
   EXEC xp_cmdshell 'DIR C:\' 
;
GO
--===== Test complete... go back to normal.
 REVERT
;

--	Succeeds (not in Jeff's original post):
	use master;
	exec xp_cmdshell 'dir c:\';

--	Fails (even though we are executing as sysadmin)
	use MyTester;
	exec GetDirInfo;

--	Succeeds (not in Jeff's original post):
	use master;
	exec xp_cmdshell 'dir c:\';

--	Fails (even though we are executing as sysadmin)
	use MyTester;
	exec GetDirInfo;

-----------------------------------------------------------------------------------------------------------------------
--===== Now, show that the "TestDummy" user can...

--	Succeeds (not in Jeff's original post):
	use master;
	exec xp_cmdshell 'dir c:\';

--	Fails (even though we are executing as sysadmin)
	use MyTester;
	exec GetDirInfo;

-----------------------------------------------------------------------------------------------------------------------
--===== Now, show that the "TestDummy" user can...

--	Fails, as expected:
	EXECUTE AS LOGIN = 'LENOVOHOME\TestUser';
	EXEC xp_cmdshell 'DIR H:\';

--	Succeeds
	REVERT;
	EXEC xp_cmdshell 'DIR H:\';