Not exactly sure how to word this but Here goes:
Current Setup: We have a Corporate SQL Server in another part of the state. We also have a local SQL Server. There is a Commercial database (EZ-Cap) that we use. Currently twice a week we take a backup of the Corporate SQL Server and restore it to our local server for our reporting purposes. Over the last few years I've built about 300 Views that are kept in another database on our local Server. The views point to the local copy of the Database. Our Data Line between Corporate and us has been expanded and Corporate want's to discontinue the backup and restore. So I'm trying to create a linked Server to the Corporate database so I can re-point those views to the Corporate Database instead of the local copy. The issue I have with that is that the Data is a Commercial Database so unless I use the UserID and Password to that Database I can't see the data. If I do use the UserID and Password to that particular Database the Link Fails because I don't have the rights on that server to link to that database. If I use the sa password and login I can connect to the database just fine but the primary tables I use for my reports are all blank because the sa username and password don't have access to the commercial data. So does anyone have an idea of how I can make this work. SQL Server Versions are Corporate (12). Local Version 8 R2. Anybody have an Idea on how I can make this work?