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

Source: Internet
Author: User
Tags management studio sql server management sql server management studio
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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.