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.
-
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 :
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.