Linked server problem

Hi.

I am promoting a data warehouse to production. Part of the ETL process
relies on a stored proc to return data to an SSIS application. This process
is causing some issue and I wonder if anyone can shed some light on it.

I have a server (A) which has a stored procedure I will call spMyProc. This
stored proc has references to server E because it needs to execute a view on
E to grab its data. the linked server in development that we have does this
fine because it uses the generic login and has been assigned the correct
permissions to select from the views and tables on E with no security issue.
In production the login is a specific one created by someone else for
collection of transaction logs from the system databases. The user in
production does not have the rights to the view or the c.50 databases on
that server that the view relies on. We are between a rock and a hardplace
because we dont want to change the login permissions to allow to see the
view and db's etc, and we don't want to modify the stored procedure either
because that brings the project back by weeks for testing etc., is there
another way? If not then can anyone tell me how to run a select statement
on the view on Server E, from server A, using crudentials supplied inside
the stored proc?

Thanks,
Nick.

I ended up creating a table in my ETL that grabs the data from the view and
puts it in the datamart. The stored procedure was then modified to look at
the table instead, which actually worked out great for me for other reasons.
If you are experiencing the same issue I would think about it a little more
before implementing my resolve.

BTW, I still do not understand why MS did not incorporate some way of having
the same server linked more than once from the same server i.e. Server A has
2 linked server links to server B. The problem is that if the link connects
and the user does not have rights to the intended object on the destination
server, then the process of execution will fail - it doesn't recognize this
and use a secondary login for some reason (probably by design!?!), it just
bombs! If this is by design, then a re-work of the design might be in order.

Nick.


"Nick Large" wrote in message

> Hi.
>
> I am promoting a data warehouse to production. Part of the ETL process
> relies on a stored proc to return data to an SSIS application. This
> process is causing some issue and I wonder if anyone can shed some light
> on it.
>
> I have a server (A) which has a stored procedure I will call spMyProc.
> This stored proc has references to server E because it needs to execute a
> view on E to grab its data. the linked server in development that we have
> does this fine because it uses the generic login and has been assigned the
> correct permissions to select from the views and tables on E with no
> security issue. In production the login is a specific one created by
> someone else for collection of transaction logs from the system databases.
> The user in production does not have the rights to the view or the c.50
> databases on that server that the view relies on. We are between a rock
> and a hardplace because we dont want to change the login permissions to
> allow to see the view and db's etc, and we don't want to modify the stored
> procedure either because that brings the project back by weeks for testing
> etc., is there another way? If not then can anyone tell me how to run a
> select statement on the view on Server E, from server A, using crudentials
> supplied inside the stored proc?
>
> Thanks,
> Nick.
>