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
@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!