Saturday, May 14, 2011

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