Spawning a Windows command shell through MS SQL Server

Most of time you will find certain tasks to be done which can be done by executing commands from the dos prompt of windows. For example you want to delete certain data files which are dettached from the server or for any other work. For that we need to have access to the server where we can open a command prompt and execute the commands.

But there is a smart way to do it from the sql server also with the help of the xp_cmdshell stored procedure. This procedires spwans the windows command shell and we can exucte neumrous commands through it which will be retured back to us as a result in the tabular format

xp_cmdshell { 'command_string' } [ , no_output ]

It can be used for various requirement. One very good example of it is given at
http://blog.sqlauthority.com/2007/04/13/sql-server-script-to-find-sql-server-on-network/

which tells how to get the list of the sql server running on the network by executing the stored procedure for more information on xp_cmdshell please visit following link 

http://msdn.microsoft.com/en-us/library/ms175046.aspx

if xm_cmdshell is disabled then first we have to enable it by executing following commands

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

but enabling it on server is risky since from network user can excute commands on the server which normally he cann’t do. So some risk are involved. which can be handled by the rights

Check who all are connected to SQL Server

To check who all are connected to sql server and what they are doing, is a very important requirement. It can be required when you want to perform some exclusive work on you database(s) or for some other work also. In order to check who all are there and connected to which database we can use following stored procedure.

–Check who all connected and doing what
exec sp_who
go

It will give you the following result 

 

Result of store procedure sp_who