How can I run a T-SQL Script using SQL Credentials?

Question

I need to develop a T-SQL insert script for use on a remote SQL 2005 Express instance but I would like the script to include the necessary SQL credentials so all the user has to do is execute the script. What is the easiest way to accomplish this?

Answer 1

As you describe it, it's not going to work. The T-SQL script needs to run on a SQL Server which means that it needs to be run in an appropriately-authenticated session.

One option would be to write the TSQL script, and then provide it with a VBScript or CMD file or PowerShell script that'll launch the TSQL at the correct server with the right credentials.

Answer 2

Using a batch file and SQLCMD, you can execute the script as below :

cls
echo off

set DbServer=Server_name\InstanceName
set MyLogin=sa
set MyPassword=StrongPassword

set MasterDbName=master
set DbName=AdventureWorks


set SQLCMDPath=C:\Program Files\Microsoft SQL Server\90\Tools\binn

set SpScripFilePath=C:\ScriptFolder
set SpScripFileName=test.sql



echo %date% %time%  process starts ...  > test.txt
echo %date% %time%
echo %date% %time% applying %SpScripFileName% to %DbName% ...
echo %date% %time% upgrading db schema with %SpScripFilePath%\%SpScripFileName% >> test.txt
"%SQLCMDPath%\SQLCMD.exe" -w 1500  -U%MyLogin% -P%MyPassword% -S%DbServer% -d%DbName% -i"%SpScripFilePath%\%SpScripFileName%"   >> test.txt

echo %date% %time%  -------------------------------
echo %date% %time%  -------------------------------     >> test.txt

Using POWERSHELL, you can use this script from Microsoft Script Center.