Unable to log on to the local database after the computer name is changed due to SQL Server problems

Problem description

 

 

Cause prediction and Verification

First, declare that the Login Failed here is not caused by errors such as the failure of SQL Server services. The general solution is not feasible. The possible cause is that the computer name is changed, and the problem is found after the problem is solved successfully. When installing the default instance of SQL Server, the authentication mode adopted by the author is Windows authentication, and the user is a user account (not a domain account) of the local machine ), therefore, when the machine name is changed, the connection will fail, because the previously installed database instance considers the user as the previous user, and there is no sa user.

Problem Solving

Fortunately, Microsoft provides a solution for running xec, which allows you to run programs with an nt authority \ SYSTEM account, which is different from the "regular" Administrator Account-which has the inherent permission to access SQLServer. (Please refer to connection http://blog.csdn.net/dba_huangzj/article/details/7927266)

Download PsExec address: http://technet.microsoft.com/en-us/sysinternals/bb897553.aspx

Note: pluxec is a component of PsTools.

After the download is complete, open CMD as an administrator and cd it to the extract directory of PsTools. Then run the following command:

Export xec-s-I "C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ Tools \ Binn \ VSShell \ Common7 \ IDE \ Ssms.exe "where" is the path of SSMS (SQL Server Management Studio)

For more information, see the first connection (add domain account and sa (if you want.

Note that the authentication mode should be set to the Hybrid Authentication mode. Otherwise, creating a new login account does not work.

Extension

I have to say that pluxec (PsTools) is a good tool and cannot be accessed or connected in the future. You can use this tool to directly simulate login, and then you can set up users or modify permissions.

Follow-up

Later, I found that the machine name could not be copied after it was changed. Sorry, Google, you can use the following script to solve this problem.

Use mastergoselect @ SERVERNAME; select serverproperty ('servername') -- if the two results are inconsistent, the machine name is changed and the above error is reported during configuration replication. -- To fix this problem -- execute the following statement and restart the SQL service IF SERVERPROPERTY ('servername ') <> @ servername begin declare @ server sysname set @ SERVER =@servername EXEC SP_DROPSERVER @ SERVER = @ server set @ SERVER = CAST (SERVERPROPERTY ('servername') as sysname) EXEC SP_ADDSERVER @ SERVER = @ SERVER, @ LOCAL = 'local' END -- Do not forget to restart the service. After the service is started, run it again.

Remember: you must restart SSMS and other related services.