xp_cmdshell is a very powerful built-in extended stored procedure that can be used to execute from within SQL Server – any external operating-system command. xp_cmdshell combined with sqlcmd utility allows administrators or developers to execute SQL scripts to Remote SQL Servers.

This xp_cmdshell configuration option is disabled by default in SQL 2005 and following SQL editions because some malicious users can perform abusive operations using this option. Access to xp_cmdshell should be restricted to highly privileged users. By default sysadmin fixed-role users have access to this procedure. Users that don’t belong to sysamin fixed role must be impersonated with a xp_cmdshell Proxy Account. I will show bellow every step in configuring and using this procedure.
The xp_cmdshell syntax:

xp_cmdshell { 'command_string' } [ , no_output ]

1. Enable xp_cmdshell on SQL environment:

In SQL 2005 /2008:

In SQL 2005 there are two ways to enable this option. Using Surface Area Configuration tool or sp_configure. Enabling this option via Surface Area Configuration tool is rather straight forward. In SQL 2008 the only option available is to use sp_configure procedure.

sp_configure:

use master
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE

2. xp_cmdshell Permissions:
 
In SQL 2005 / 2008:
 
2.1. Grant sysadmin fixed – role – to highly privileged users. Users that belong to sysadmin server fixed-role will have the right to execute the xp_cmdshell procedure.

2.2. Use a Proxy account – to be assigned to non-admins SQL users.

First step is to create a credential ##xp_cmdshell_proxy_account## using  a Windows/Domain account credentials – member of sysadmin role. If a non-admin user will execute xp_cmdshell procedure SQL will use the proxy credential instead of the executing user’s credentials.
In short, the credential account’s security context will be used to actually run xp_cmdshell by users that are not members of the sysadmin role.

USE master
GO
-- Create the SQL login which will use xp_cmdshell.
CREATE LOGIN [SQL_User_Impresonated] WITH PASSWORD='Sql_P@ssword'
-- Create a proxy credential for xp_cmdshell assigned to a Domain/Windows account.
EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\LOGINNAME', 'LOGINNAME_PASSWORD';
-- Grant database access to the SQL Server login account that you want to provide access.
EXEC sp_grantdbaccess 'SQL_User_Impresonated'
-- Grant execute permission on xp_cmdshell to the SQL Server login account.
GRANT exec ON sys.xp_cmdshell TO [SQL_User_Impresonated]
GO

Test the Privileges granted:

Login with SQL_User_Impresonated / Sql_P@ssword and run:

exec xp_cmdshell ‘ipconfig /all’

You will receive a list of network addresses from the remote Server.

3. How to use sqlcmd with xp_cmdshell:

sqlcmd utility is a Microsoft Win32 command prompt utility meant to run ad hoc Transact-SQL statements and scripts. It lets you connect to an SQL Server from your network using either SQL or Windows authentication and launch SQL scripts passed as SQL queries or as input SQL files. More about this you can find on MS site here and here because there is covered almost each one of the sqlcmd possible usages.

Among many other things, I make usage of xp_cmdshell to collect databases from all monitored servers. I will show how you can achieve this.

The compacted version of sqlcmd syntax is:

sqlcmd
[ -S [protocol:]server[\instance_name][,port] ]
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -d db_name ]
[ -i input_file ] [ -o output_file ]
[ -q "cmdline query" ]

Create the databases.sql file with following content:

SELECT name FROM master.dbo.sysdatabases where has_dbaccess(name) = 1

Save this file somewhere locally on the server from which you want to launch remotely the T-SQL script and on which you have enabled xp_cmdshell.
sqlcmd can be used both with sql authetication as well as windows to access the remote server. If you want to use Windows don’t specifiy -U and -P paramteres but -E (trusted connection). I will use in my example an user and a password with permissions on the Remote SQL Server to read master database objects. For security reasons windows authentication is preffered when executing the command from SQL Agent. It will run under SQL Agent Service Account security context.

In SSMS, connect to the SQL Server Instance from which you want to launch remotely the locally stored T-SQL script, using the above created user with xp_cmdshell permission (SQL_User_Impresonated)

Run:

exec xp_cmdshell 'SQLCMD -S Remote_SQL_Server -U Remote_User -P Remote_User_password -i "C:\...\databases.sql" -o "C:\...\databases.txt"'

Check in the databases.txt output file that the database list has been inserted and the database names are the ones from the Remote server.
Here you go, you can use this in more complex scripts to collect different information from system databases.