Execute Operating System commands or executables

XP_CMDSHELL

run Operating Systems commands from SQL Server.

1. Enable the feature:

or, using the following command sequence:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

2. Create a proxy ##xp_cmdshell_proxy_account##

If VIP\NICK account with password 'c@nada1' exists,

EXEC sp_xp_cmdshell_proxy_account 'VIP\NICK','c@nada1'

3. Create SQL Login- aLogin and map it to database user: aLogin in master database

CREATE LOGIN aLogin WITH PASSWORD= 'c@nada1'
GO
CREATE USER aLogin FOR LOGIN aLogin
GO

4. GRANT execute permission

GRANT EXECUTE ON xp_cmdshell TO aLogin

5. Log onto SQL server as aLogin

If VIP\NICK has the required permission on the file system, there is no mistake to run the following commands:

exec master.dbo.xp_cmdshell 'md c:\Data'

exec master.dbo.xp_cmdshell 'del d:\data\test\* /q'

======================================

Create Operating System (CmdExec) job step

You must use full path to reference the command. For example,

C:\WINDOWS\system32\notepad.exe

1. Create a credential and a proxy for the Operating System (CmdExec) subsystem

CREATE CREDENTIAL NICK WITH IDENTITY ='VIP\NICK',SECRET='c@nada1'

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'nickCmd',@credential_name=N'NICK',
@enabled=1

2. Create a job with the job step to run:

C:\WINDOWS\system32\notepad.exe

3. Test the job.

So, you could write a batch file to include all the commands and put it into the job step.