Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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