Enabling MS SQL Server to run Command Line Applications

For Microsoft SQL Server to be able to run windows command line applications the following query must be run.

USE master

GO

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'xp_cmdshell', 1

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure 'show advanced options', 0

GO

Allow a User to Run All Stored Procedure in a Database

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GRANT EXECUTE TO db_executor

This creates a role called db_executor. Anyone given this role will be able to run all the stored procedures in the database, including new ones added after the user is given the role.


You can either give the user the executor role via SSMS or you can run this sql command against the database you want the user to have access on.

EXEC sp_addrolemember 'db_executor', [LOGIN_NAME]

For Active Directory domain names use '[domain\username]'

WITH (NOLOCK)

Using the NOLOCK hint can speed up queries by allowing the query to not wait while other queries are writing to the table. The downside is that your query can read data that may get rolled back.

FROM table1 a WITH (NOLOCK)

JOIN table 2 b WITH (NOLOCK) ON a.field = b.field

JOIN table3 c WITH (NOLOCK) ON b.field= c.field

JOIN table4e WITH (NOLOCK)

More info: http://msdn.microsoft.com/en-us/library/ms187373.aspx