SQL server linked server to itself

I had a problem – I wanted to put archive data into separate databases on the same SQL Server 2005 system. When I did this though, I had to map all the accounts I had previously created on the main database into each of my annual archive databases. The meant that a lot of synchronising of accounts and logins across each of the archives, and creating new ones whenever a new archive was created.

I knew that the SQL Server 2005 linked servers system allowed one server to log into a different server using a single login, but I could not see a way to do this when the server was local. Then I realised that I could create a linked server called “localhost” thus:

— add linked server using alias to self!
sp_addlinkedserver @server=’localhost’, @srvproduct=’SQL Server’;
— create a login map to a single account
sp_addlinkedsrvlogin
@rmtsrvname=’localhost’,
@useself=’FALSE’,
@rmtuser = ‘MyArchiveUser’,
@rmtpassword = ‘myarchivepassword’;

SQL Server thinks this that ‘localhost’ is a “remote” server and allows me to use it as if it were. I could then make my archives available as views in the live database as follows:

CREATE VIEW ArchiveTable AS
SELECT * FROM localhost.ArchiveDatabase.dbo.ArchiveTable;

Really pretty simple once I’d figured out how to fool SQL!

Advertisements

2 thoughts on “SQL server linked server to itself

  1. Thanks for your article. I had a similar server where I was testing a script locally that was intended to be executed against two different linked servers. I just ran the sp_addlinkedserver command once for each server name, and then added an entry for each hostname in my hosts file, pointing to 127.0.0.1. Worked a treat!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s