Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute Stored procedure and SSIS Packages in different servers Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 1:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 30, 2014 3:29 AM
Points: 10, Visits: 124
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,
Roshan



Post #1352032
Posted Thursday, August 30, 2012 9:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
Hi roshanmaugustine. I am not trying to be rude here but I must say, I am sure that what you posted makes sense in your head, but reading it without knowing your environment created more questions for me than answers. Please give specific details as to your environment and situation. What kind of application? ASP.NET or is this a Windows Forms app located on the user's desktop? What do you envision the user executing directly?

- a stored procedure that executes an SSIS package
- an SSIS package that executes stored procedures
- both stored procedures and SSIS packages directly


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1352336
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse