SQLSERVER mistakenly deletes the SA password for a Windows login user

I was anxious to ask a question, a DBA deleted the Windows login user, and the SQL Server server authentication method was Windows authentication. What should I do ?? The answer I gave him at the time was: reinstall the system database master. I saw this article today without the SA password and cannot Log On As a Windows Integrated Identity. What should DBA do ?, False thinking

I was anxious to ask a question, a DBA deleted the Windows login user, and the SQL Server server authentication method was Windows authentication. What should I do ?? The answer I gave him at the time was: reinstall the system database master. I saw this article today without the SA password and cannot Log On As a Windows Integrated Identity. What should DBA do ?, False thinking

I was anxious to ask a question, a DBA deleted the Windows login user, and the SQL Server server authentication method was Windows authentication.

What should I do ??

I replied to him at the time: reinstalling the system database master

Today, I saw this article without the SA password and cannot log on to Windows Integrated Identity. What should I do with DBA ?, Thinking

Let's assume we have a bad situation.

Sa is disabled, server authentication is in Windows Authentication mode, Windows logon users are deleted, and no logon users with other sysadmin roles

Step 1:

Stop SQLSERVER: stop mssqlserver on the command line.

Step 2:

Go to the installation directory of SQLSERVER

Add the/m/f parameter.

Step 3: Start SQLSERVER in single-user mode

Step 4: Enable SSMS

At this time, do not connect immediately. You need to click Cancel and then click Create query in the upper left corner. This step is the same as that of DAC (dedicated administrator connection ).

You will find that Windows users can log on at this time

Step 5: execute the following SQL script

1 -- enable the xp_cmdshell function 2 EXEC [sys]. [sp_configure] @ configname = 'xp _ cmdshell', -- varchar (35) 3 @ configvalue = 1 -- int 4 reconfigure with override 5 6 7 -- modify the registry, change authentication to a hybrid authentication method. 8 USE [master] 9 GO10 EXEC xp_instance_regwrite N 'HKEY _ LOCAL_MACHINE ', N 'softwaremicrosoftmssqlservermssqlserver', N 'loginmode', REG_DWORD, 211 GO12 13 -- create login name 14 create login [Computer Name Administrator] from windows; 15 GO16 17 -- Grant the LOGIN name permission to sysadmin18 USE master19 GO20 EXEC [sys]. [sp_addsrvrolemember] @ loginame = 'computer name admin', -- sysname21 @ rolename = sysadmin -- sysname22 23 -- disable xp_mongoshell function 24 EXEC [sys]. [sp_configure] @ configname = 'xp _ cmdshell', -- varchar (35) 25 @ configvalue = 0 -- int26 reconfigure with override

At this time, the authentication method has been changed to the hybrid authentication method.

Step 6: Disable SQLSERVER and restart

Open SQLSERVER Configuration Manager and start SQLSERVER

Step 7: log on to SQLSERVER

Back to SSMS, we can see that this time is back to normal

Summary

Thanks to the i6first hero, he had always thought that he could not get started. He could not think that he had thought of a single-user mode to start SQLSERVER.

Without the SA password, you cannot Log On As a Windows Integrated Identity. What should you do with DBA?

A colleague reported that SQL could not be properly logged on. I used to log on through windows Integrated Identity Authentication (the sa password was forgotten). Today I changed the server machine name, currently, you cannot log on to the console.

If you use windows integrated authentication when logging on to SQL, the logon box displays the logon name in the format of "machine name current system username". The logon name and password are gray and cannot be entered.

I learned that my colleague has just modified the server's machine name, so the "new machine Name Current System User Name" is displayed in the SQL login box ". You need to know that the reason why windows Integrated Identity Authentication can log on is that this user name has been included in the SQL login name. The original user name was recorded in the SQL when the SQL was installed, if the machine name is changed, the "new machine Name Current System User Name" cannot log on normally.

Someone said on the internet can use the OSQL-S instancename-E (input in the command line window) login and then modify the sa password, after some attempts found to be talking, this is because the windows Integrated Identity is required to log on.

Later, I saw a document on Microsoft's official website. I only needed to add the "-m" option in the SQL startup parameters (remember to add the "-m" option before-m ), then, restart the SQL service and use the windows Integrated Identity Authentication of SQL Management Studio to log on again.

-M indicates logon by a single user. Careful readers may question: I only added-m, but I still used windows integrated identity verification. In theory, what is the difference from using SQLOS-S instancename-E?

This question is very professional. I didn't understand it at the time, and I thought it was a bit incredible, but the result was like this, and there must be some truth. Later, I found this article on Microsoft's official website.