-1

I'm going to create a script to execute numerous SQL files. I've chose to use SQLCMD instead of ADODB because it provides logging information quickly, and we're executing T-SQL. I'm testing out SQLCMD.exe before putting it into the script.

Connecting to the database using SQLCMD.exe, I utilize -E for Windows Authentication. This means that I need to use my network account, which does not have elevated access on any given machine. However, SQLCMD.exe requires elevated access in order to execute a query, otherwise I receive the following error:

Sqlcmd: Error: Error occurred while opening or operating on file (Reason: Access is denied).

After running the following command:

sqlcmd.exe -S <SQLSERVER,PORT> -d <DATABASE> -i scriptfile.sql -o scriptfile.log -E

How can I utilize SQLCMD.exe with elevated local permissions (Administrator) but use Windows Authentication to connect to the server (using a non-Administrator account)? I'd prefer not to change any permissions of SQLCMD.exe, as there may be multiple people that use this script that I'm not keen on informing them of this prior instruction.

Mike Sanders
  • 49
  • 1
  • 8
  • what was the full command you tried? – Vamsi Prabhala Oct 23 '15 at 15:18
  • 1
    I think the error you are seeing is related to a script file or log file you are referencing. Not whether or not you are using windows auth. Can you post the full command to give us a better idea of what's going on? – CDC Oct 23 '15 at 15:25
  • Sure here is the full command sqlcmd.exe -S -d -i scriptfile.sql -o scriptfile.log -E – Mike Sanders Oct 23 '15 at 15:52
  • Where's the VBScript? That is the raw `sqlcmd` not the VBScript you used to execute it. – user692942 Oct 23 '15 at 15:58
  • I'm testing the sqlcmd before introducing it into the script. – Mike Sanders Oct 23 '15 at 16:01
  • And what path are you running the command from when you test it? Does the non-administrator running the command have access to read from `scriptfile.sql` and access to write to `scriptfile.log`? Most commands generally default to the current working directory. – Bacon Bits Oct 23 '15 at 16:14
  • That was it! I was storing the script in the same folder as SQLCMD.exe as testing purposes, and wasn't giving the user permissions. These scripts are on a network drive, so I can use pushd/popd to access with the VBScript. Thank you for the help, it was a dumb mistake. – Mike Sanders Oct 23 '15 at 16:18

1 Answers1

1

What path are you running the command from when you test it? Does the non-administrator running the command have access to read from scriptfile.sql and access to write to scriptfile.log? Most commands generally default to the current working directory.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • It wasn't that I did not have access to SQLCMD. As Bacon Bits stated above, I was attempting to execute a script that the non-Administrator user did not have access to. – Mike Sanders Oct 23 '15 at 16:21