The strange Problem of database link caused by renaming computer

A few days ago, when deploying a test database to the development department, there was a very strange problem: When creating a linked server GEK-MIS01, the error is as follows:

Msg 15190, Level 16, State 1, procedure sp_dropserver, line 56th

There are still remote logins or links to the server ' Gek-mis01 '.

The script is as follows (omit key information such as login name):

View Code

Because it was a batch of script execution and only this script error, when I am ready to check the cause of the error, there are more urgent things to deal with, the delay in dealing with this problem, development there in the test process found that the database link has a problem, the mail feedback to me, I checked unexpectedly found a lot of incredible phenomenon:

(1): I use SSMs into the "server object"-"linked server", actually can't see this linked server object, and through sysservers and other system table can find this server link object information, at that time I was baffled, Nani ah?

(2) Re-execution of the above script found that the deletion of the linked server script will not delete the linked server, and add the linked server when the error

IF EXISTS (SELECT srv.name from sys.servers srv WHERE srv.server_id! = 0 and Srv.name = N ' gek-mis01 ')

EXEC master.dbo.sp_dropserver @server =n ' gek-mis01 ', @droplogins = ' droplogins '

GO

EXEC master.dbo.sp_addlinkedserver @server = N ' gek-mis01 ', @srvproduct =n ' SQL Server '

Msg 15028, Level 16, State 1, procedure sp_addlinkedserver, line 82nd

The server ' gek-mis01 ' already exists.

(3): Next I tested the use of the next linked server, found that some logins (Windows authentication, the sysadmin role) use the database link is not a problem, and SQL Server Authentication logon name will report the following error:

EXEC [Gek-mis01]. DatabaseName.dbo.Procedure ' xxxx '

MSG 916, Level 14, State 1, line 1th

The server principal "username" Cannot access the database "xxxxx" under the current security context.

(4): While in SSMs, under "Server object", "Linked server", "Gek-mis01", when you click the directory, the following error is reported.

The specific error message is as follows:

View Code

Workaround: To view the current user as guest, execute the following weighting statement to resolve the issue

SELECT Current_User;

GRANT EXECUTE on SYS. Xp_prop_oledb_provider to Guest;

But for the phenomenon of the two, I have been troubled for a long time, has been thought to be the authority of the problem, finally after the lengthy investigation of blood vomiting, finally found a cause of vomiting blood: This test database server The original computer name is GEK-MIS01, Later I did not know what the reason was used as a test server by the system administrator (for historical reasons I did not say more), renamed to GETTESTNT15, and a new server named Gek-mis01, the result of my new link server today, I stepped on the mine.

The next solution is more smooth, first delete the linked server, repair the computer renaming problem, re-create the linked database, OK, problem solved!

Code Snippet

1. exec sp_droplinkedsrvlogin ' gek-mis01 ', ' xxxxx '
3. exec sp_dropserver ' gek-mis01 '
6. Use master;
8. GO
10. IF serverproperty (' servername ') <> @ @SERVERNAME
12. BEGIN
14. DECLARE @server sysname;
16. SET @server [email protected] @SERVERNAME;
18. EXEC [email protected]= @server;
20. SET @server = CAST (serverproperty (' servername ') as sysname);
22. EXEC [email protected][email protected], @local = ' local ';
24. END

The strange Problem of database link caused by renaming computer