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?
1
3
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
3
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.
