xp_cmdshell exec permission for non-sysadmin user

(1) Enable the xp_cmdshell procedure
Enable xp_cmdshell by using sp_configure or "Surface Area Configuration"
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
(2) Create a login 'Domain\TestUser' for the non-sysadmin user that has public access to the master database
(3) Grant EXEC permission on the xp_cmdshell stored procedure
     GRANT EXECUTE ON xp_cmdshell TO [Domain\TestUser]
(4) Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account
      EXEC sp_xp_cmdshell_proxy_account '[Domain\TestUser]','pwd'
Note: pwd means windows password for [Domain\TestUser] account id on the box.
(5).Grant control server permission to user
USE master;
GRANT CONTROL SERVER TO [Domain\TestUser]
GO

INSERT Data from XL Sheet to SQL Server Database Table

--1.XL sheet to Table
Insert into Table2 Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\test.xls;HDR=YES',
    'SELECT * FROM [Sheet1$]')
--2.Select data from xl and display in SQL Server Management
Select * from openrowset('microsoft.jet.oledb.4.0',
'Excel 8.0;database=D:\Test.xls',
'select * from [Sheet1$]')
--3.Insert data from SQL Table to XL sheet.
Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\Test2.xls;',
    'SELECT * FROM [Sheet1$]') select * from Table2

Backup & Restore permissions

1.Backup

--User/Batch id should be db_backupoperator role or 'SysAdmin' role

2.Restore
--User/Batch id should be dbcreator role or 'SysAdmin' role
--Or If User/Batch id have administror privileges on windows server level,,then database can restore without dbcreator role or 'SysAdmin' role.

Database Backup & Restore using Batch scripts

--Backup batch file script
SQLCMD -S SQLInstance1 -E -i "c:\test\Backupquery.sql"

--Backupquery.sql script
--Get Date format to YYYYMMDDHHMM

Declare @BkpFiledate varchar(255)
set @BkpFiledate=CONVERT(VARCHAR(20), GETDATE(), 112) +CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()))+CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112)

--print 'BkpFiledate'+@BkpFiledate
DECLARE @BkpFilePath NVARCHAR(255), @BkpFileName NVARCHAR(255), @BkpFullPath NVARCHAR(255)
SET @BkpFilePath = 'C:\Test\'

print '['+convert(varchar(25),Getdate())+'] '+'Start BACKUP DATABASE TestDB ';
SET @BkpFileName = 'Backup\TestDB_backup_'+@BkpFiledate+'.BAK'
SET @BkpFullPath = @bkpFilePath + @bkpFileName
BACKUP DATABASE TESTDB TO DISK = @BkpFullPath WITH INIT, STATS=10
print '['+convert(varchar(25),Getdate())+'] '+'End BACKUP DATABASE TestDB ';
--------------------------------------------------------------------------------------

--Restore batch file script
set bkpLocation=C:\Test\Backup
set Data1Location=C:\Test\Data1
set Log1Location=C:\Test\LOG1

set DBName=TestDB2
REM Finds the FOR loop for recent backup file on %BackupLocation%\%DBName%\ location
FOR /F %%I IN ('DIR "%BkpLocation%\%DBName%\*.BAK" /B /O:D') DO set RecentBackupFile=%%I

SQLCMD -S SQLInstance2 -E -d master -Q "RESTORE DATABASE %DBName% FROM DISK=N'%BkpLocation%\%DBName%\%RecentBackupFile%' WITH FILE = 1,
MOVE N'%DBName%_Data' TO N'%Data1Location%\%DBName%.mdf',
MOVE N'%DBName%_Log' TO N'%Log1Location%\%DBName%_1.ldf',
REPLACE,STATS=10"

Commandline exec SQL Agent job

1.SQLCMD -SBKCOM\INSTANCE1 -E -Q"EXEC msdb.dbo.sp_start_job @job_name ='TEST'"

Permissions--On MSDB Database "SqlAgentOperator" role required to run the sql agent jobs the through command line.

2.isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

3.osql -E -d MSDB -Q "sp_start_job 'MySQLJob'"
4.CREATE PROCEDURE sp_RunJob
AS
BEGIN
EXEC msdb..sp_start_job @job_name = 'The job name'
END

SSIS Pacakge Execute

1. http://msdn.microsoft.com/en-us/library/ms162810.aspx
2. http://www.codeproject.com/KB/database/Call_SSIS_from_SP.aspx

--Using Stored procedure
http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx
http://geekswithblogs.net/stun/archive/2010/02/24/execute-ssis-package-from-stored-procedure-with-parameters-using-dtexec-utility.aspx


Syntax

dtexec /option [value] [/option [value]]...

Using dtexec from the xp_cmdshell
You can run dtexec from the xp_cmdshell prompt. The following example shows how to run a package called

UpsertData.dtsx and ignore the return code:
EXEC xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'

The following example shows how to run the same package and capture the return code:
DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx"'

SSIS Pacakge - Exit codes returned from dtexec utility

Value        Description

0           The package executed successfully.

1           The package failed.

3          The package was canceled by the user.

4          The utility was unable to locate the requested package. The package could not be found.

5          The utility was unable to load the requested package. The package could not be loaded.

6          The utility encountered an internal error of syntactic or semantic errors in the command line.