none
Execute Stored Procedures from windows batch files RRS feed

  • Question

  • We have got a requirement to execute SP's from a windows batch file. I was able to create the file as below
    @Echo off
    ::Execute Stored Procedure
    SQLCMD -S <server_name> -d <db_name> -Q "exec dbo.testsp" -o "A:\Logs\testsp_log_%date:~10,4%%date:~4,2%%date:~7,2%.txt"
    However, by default it uses Windows Authentication and it should not be. I'm thinking of using -U and -P parameters, however password is visible when it is hard-coded in the batch file. Is there any alternative method i.e., encrypting the password in the batch file. Also, i have created the SP as "WITH EXECUTE as OWNER"..so that it inherits the object/schema owner privileges that is dbo for executing the SP. So, i just need an encrypted way to enter into SQL server and execute the SP. Any advise? 

    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Tuesday, November 12, 2019 7:04 PM

Answers

  • Hi Kris_SQL,

    Thanks for posting here.

    For your requirement, there are two way you could refer to.

    1. Add -U parameter to your batch file, and the content could be like this.

    @Echo off
    ::Execute Stored Procedure
    SQLCMD -S <server_name> -d <db_name> -Q "exec dbo.testsp" -U username -o "A:\Logs\testsp_log_%date:~10,4%%date:~4,2%%date:~7,2%.txt"

    When you execute that file, it would pop-up the prompt window to remind you to type the user password.

    2. You need to set file security for that batch file, anyone who has no read permission couldn't view the content of the file.

    Hope above could be useful to you.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Kris_SQL Wednesday, November 13, 2019 2:31 PM
    Wednesday, November 13, 2019 3:15 AM
    Moderator

All replies

  • Hi Kris_SQL,

    Thanks for posting here.

    For your requirement, there are two way you could refer to.

    1. Add -U parameter to your batch file, and the content could be like this.

    @Echo off
    ::Execute Stored Procedure
    SQLCMD -S <server_name> -d <db_name> -Q "exec dbo.testsp" -U username -o "A:\Logs\testsp_log_%date:~10,4%%date:~4,2%%date:~7,2%.txt"

    When you execute that file, it would pop-up the prompt window to remind you to type the user password.

    2. You need to set file security for that batch file, anyone who has no read permission couldn't view the content of the file.

    Hope above could be useful to you.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Kris_SQL Wednesday, November 13, 2019 2:31 PM
    Wednesday, November 13, 2019 3:15 AM
    Moderator
  • Thanks for your reply. This batch file will be executed by Autosys with an ssh connection to windows server from a unix box. I can't use the 1st option. For 2nd option, i should be granting r/w access to the account that triggers the batch commands?

    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Wednesday, November 13, 2019 10:37 AM