Fix SQL Server instance name consistency after Windows host name repair

1. Verify consistency

SELECT @ @SERVERNAME
SELECT serverproperty (' machinename ')

After modifying the Windows host name, the display is inconsistent when viewed with the above command and needs to be repaired.

2. Use the following script to perform and repair
DECLARE @server_name varchar (a);
Set @server_name = (select @ @SERVERNAME);
DECLARE @SERVER_PROPERTY varchar (a);
Set @SERVER_PROPERTY = CONVERT (varchar () (SELECT serverproperty (' machinename '));
exec sp_dropserver @server = @server_name, @droplogins = null;
exec sp_addserver @[email protected]_property, @local = ' local ', @duplicate_ok = null;
DECLARE @login_server_name_cmd varchar (+);
DECLARE @LOGIN_SERVER_PROPERTY_CMD varchar (n);
DECLARE @LOGIN_SERVER_PROPERTY_RCMD varchar (n);
Set @login_server_name_cmd = ' DROP login [' [email protected]_name+ ' \administrator] '
Set @LOGIN_SERVER_PROPERTY_CMD = ' CREATE LOGIN [' [Email protected]_property+ ' \administrator] from the WINDOWS with Default_database=[master], default_language=[Simplified Chinese] ';
Set @LOGIN_SERVER_PROPERTY_RCMD = ' master '. sp_addsrvrolemember @loginame =[' [email protected]_property+ ' \administrator], @rolename = [sysadmin] '
IF EXISTS (SELECT * from sys.server_principals WHERE name [email protected]_name+ ' \administrator ')
exec (@login_server_name_cmd);
exec (@LOGIN_SERVER_PROPERTY_MD);
exec (@LOGIN_SERVER_PROPERTY_RCMD);

Note: for sqlserver2008, use SA or other user with sysadmin permission to log on.

3. Restart SQL Server database in effect

This article is from the "composer" blog, make sure to keep this source http://zuoqujia.blog.51cto.com/9151800/1437704