Linked Server - login trying to access database that is not default Trying to create a linked server from server A (2005) to B (2008 R2). Everything worked when created. The login on server B was mapped to two databases: DataBase1 and Database2. Database1 was the default.

Database2 was taken off-line. When opening the User Mapping, there is a message, "One or more databases are inaccessible and will not be displayed in list."  I assume Database2 is the culprit. I brought Database2 online and unchecked it.

When testing the connection on the Linked Server, it fails because it can't access Database2. Again, Database1 is the default.

I rebuilt the login using the exact same script with no mention of Database2, still get the error. 

Because I need a different Linked Server name that does not match the actual SQL Server name, let's say I want to link to server "A" but call the link "X"

EXEC master.dbo.sp_addlinkedserver @server = N'X'
, @srvproduct=N'Microsoft OLE DB Provider for ODBC'
, @provider=N'MSDASQL', @datasrc=N'X'
, @provstr=N'DRIVER={SQL Server};SERVER=B;UID=test;PWD=*******;'


I can go into SSMS and create a linked server through the GUI. Picking SQL Server, which won't allow me to use a name for the linked server except the same name as the server and it works. This does not use the OLE DB Provider for ODBC.

EXEC master.dbo.sp_addlinkedserver @server = N'B', @srvproduct=N'SQL Server' 


Can I script this so I can indicate the link name as X and the server or datasrc?
I would also change the parameters as follows:


@srvproduct = 'sql_server'
@provider = 'SQLNCLI' if you're coming from 2005 (and 'SQLNCLI10' from 2008/8R2, and 'SQLNCLI11' from 2012, etc.)
@provstr can be removed entirely; it's not necessary with this product and provider; your linked server logins will handle that.


Whatever SQL Server login is being used by the linked server probably has an offline/removed default database.  Change the default database for that login to one that exists and that the user has permissions to and try again.

This is a guess, as you didn't post the CREATE USER script that generated the particular username logging in.