Execute Stored procedure and SSIS Packages in different servers RRS feed

  • Question

  • Hi All, I have to execute some stored procedure and SSIS packages (It copies data from one to table to another table between two databases).It can be within the same server or in different server. I should use SQL server Authentication to execute these procedures and packages. But Application is using windows authentication to logging to the application I have one table which stores the userid and password in encrypted format. Table structure for the above Process_ID (PK) Process_Name Source_ServerName Target_ServerName Source_DBName Target_DBName UID PWD From front end application we can choose the Process_Name (SP Name or PackageName) IF the Logged in used has the rights to execute the Process then from SQL we have to choose the default UID and Password and execute the process using this How can I achieve this, Which are the minimum rights should I provide to this SQL User, Which is the best method to connect two SQL server

    Thanks in Advance,


    • Edited by roshan321 Monday, August 27, 2012 11:57 AM
    Monday, August 27, 2012 11:55 AM

All replies

  • >>>> Which is the best method to connect two SQL server

    Recommended way is to connect with windows Authentication... along with EXECUTE permission on that stored procedure.See example

    GRANT EXEC ON some_sp TO user1-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user2-- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO user3-- Grant EXEC permission all procedures in the database.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Tuesday, August 28, 2012 5:28 AM
  • Hi Uri Diamnt,

    Thanks for your reply

    Is there any way we can store the userid and Password in table and runtime we can use this userid and pwd to execute the procedure and SSIS Packages



    Thursday, August 30, 2012 6:10 AM