What is XP_CMDSHELL ?
XP_CMDSHELL - extended stored procedure.
With this extended stored procedure you have the ability to run any command line process within your SQL script, so you can embed this within your stored procedures, jobs or batch processing.
In SQL Server 2000 this option is enabled by default, but to limit access to using xp_cmdshell only members of the sysadmin server role have default rights.
To extend rights to other users you can use the GRANT statement to allow execute rights.
With SQL Server 2005 the use of xp_cmdshell is turned off by default. As SQL Server continues to mature Microsoft has put many safeguards in place to minimize the security risks of unwanted code being executed within SQL Server or outside of SQL Server.
For example. If the User dose not have access to the physical SQL Server machine, but if he want to see all the files in the “C:\temp” directory that have a “.sql” extension. He can do this by issuing the following command:
2) Create a new directory using XP_CMDSHELL
This is the simple example Imagine a user is in process of building a new SQL Agent job using Enterprise Manager. To organize the output from all the steps in this new job, he want the output of each step to go into a common directory on the server, where each step has its own output file. The only problem is the new directory does not exist. here we assume that used do not have access to the server, or do not want to walk down the hall to the computer room to create this new directory. No problem he can just enter the following command in Query Analyzer from the privacy of your office to accomplish creating the new directory:
exec master.dbo.xp_cmdshell ‘mkdir “c:\temp\SQL Agent Output\new_job\*
3) Run DTS package via stored procedure using XP_CMDSHELL
As you can see, it is very easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run executables? The answer is YES! Suppose you have a DTS Package that you want to run via a stored procedure. There is no “sp_executeDTS” stored procedure, but you can use the following xp_cmdshell command to run your DTS package:
Note :This command executed the “dtsrun” executable with the necessary parameters to run the “Export Invoices” DTS package. Are you starting to see how the xp_cmdshell extented store procedure can provide you with some alternatives and also expands the functionality you can build into a T-SQL script.
With this extended stored procedure you have the ability to run any command line process within your SQL script, so you can embed this within your stored procedures, jobs or batch processing.
In SQL Server 2000 this option is enabled by default, but to limit access to using xp_cmdshell only members of the sysadmin server role have default rights.
To extend rights to other users you can use the GRANT statement to allow execute rights.
With SQL Server 2005 the use of xp_cmdshell is turned off by default. As SQL Server continues to mature Microsoft has put many safeguards in place to minimize the security risks of unwanted code being executed within SQL Server or outside of SQL Server.
Examples of XP_SMDSHELL Commands
1) XP_CMDSHELL Directory SearchFor example. If the User dose not have access to the physical SQL Server machine, but if he want to see all the files in the “C:\temp” directory that have a “.sql” extension. He can do this by issuing the following command:
exec master.dbo.xp_cmdshell ‘dir c:\temp\*.sql’
2) Create a new directory using XP_CMDSHELL
This is the simple example Imagine a user is in process of building a new SQL Agent job using Enterprise Manager. To organize the output from all the steps in this new job, he want the output of each step to go into a common directory on the server, where each step has its own output file. The only problem is the new directory does not exist. here we assume that used do not have access to the server, or do not want to walk down the hall to the computer room to create this new directory. No problem he can just enter the following command in Query Analyzer from the privacy of your office to accomplish creating the new directory:
exec master.dbo.xp_cmdshell ‘mkdir “c:\temp\SQL Agent Output\new_job\*
3) Run DTS package via stored procedure using XP_CMDSHELL
As you can see, it is very easy to execute DOS commands using xp_cmdshell. So can xp_cmdshell also run executables? The answer is YES! Suppose you have a DTS Package that you want to run via a stored procedure. There is no “sp_executeDTS” stored procedure, but you can use the following xp_cmdshell command to run your DTS package:
exec master.dbo.xp_cmdshell ‘dtsrun -E -Sserver1 -N”Export Invoices”‘
Note :This command executed the “dtsrun” executable with the necessary parameters to run the “Export Invoices” DTS package. Are you starting to see how the xp_cmdshell extented store procedure can provide you with some alternatives and also expands the functionality you can build into a T-SQL script.
No comments:
Post a Comment