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?


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.

| improve this answer | |
  • That makes perfect sense. Would I be able to authenticate as a SQL user using that method? I would use the authenticated Windows user but I have them set, in the database, with read-only access for security reasons. SA is what I would like to use. – Gryphoenix May 23 '13 at 16:09

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

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.