sa密码不满足强密码要求_恢复丢失的SA密码

编程知识更新时间:2023-05-02 23:38:28

sa密码不满足强密码要求

In this article, we will review how to recover SA password by starting SQL Server in single user mode when you forget “sa” credentials or lose the access to the instances due to the following reasons:

在本文中，由于以下原因，当您忘记“ sa ”凭据或失去对实例的访问权限时，我们将回顾如何通过以单用户模式启动SQL Server来恢复SA密码：

You configured a new SQL Server instance and lost or forgot the “sa” password
您配置了新SQL Server实例，并丢失或忘记了“ sa ”密码
All the users who are part of sysadmin server level role are removed accidentally
属于sysadmin服务器级别角色的所有用户被意外删除
All the users who are part of sysadmin server level role are disabled accidentally
属于sysadmin服务器级别角色的所有用户均被意外禁用
Lost sysadmin access to the SQL Server instance
失去系统管理员对SQL Server实例的访问权限

Consider this scenario:

考虑这种情况：

Initially, I was a member of the [BUILTIN\Administrators] group. To illustrate this scenario, I connected to SQL Server using Windows authentication and I deleted all the logins in the SQL Server instance. Please refer to the below image that shows all the logins that were deleted and the login “sa” is disabled.

最初，我是[BUILTIN \ Administrators]组的成员。为了说明这种情况，我使用Windows身份验证连接到SQL Server，并删除了SQL Server实例中的所有登录名。请参考下图，该图显示了所有已删除的登录名，并且禁用了登录名“ sa” 。

I closed the connection and tried to connect the SQL Server instance using the same account which I used earlier. I am unable to log in as I deleted all the logins and the group [BUILTIN\Administrators] from which I gained access earlier. Please refer to the below image that shows login failure error.

我关闭了连接，并尝试使用之前使用的相同帐户连接SQL Server实例。我删除了所有登录信息和较早获得访问权限的组[BUILTIN \ Administrators]，因此无法登录。请参考下图，显示登录失败错误。

Now, I’ve lost access to the SQL Server instance. I do not have any login that can connect to the SQL Server instance. I do not know the “sa” login password or even if the “sa” login is disabled.

现在，我已经无法访问SQL Server实例。我没有任何可以连接到SQL Server实例的登录名。我不知道“ sa”登录密码，或者即使“ sa”登录已禁用。

In this situation, to gain access to your databases, we can re-install the SQL Server instance and attach the databases. This process may take more time and effort.

在这种情况下，要获得对数据库的访问权限，我们可以重新安装SQL Server实例并附加数据库。此过程可能需要更多时间和精力。

To gain access to the SQL Server instance back or recover SA password, please follow the below steps.

要重新访问SQL Server实例或恢复SA密码，请按照以下步骤操作。

以单用户模式启动SQL Server (Start SQL Server in single user mode)

We need to start SQL Server in single user mode by adding the parameter -m or –f in the startup parameters. Open SQL Server configuration manager and select the service of SQL Server instance. Right-click and click on the Properties option.

我们需要通过在启动参数中添加参数-m或– f来以单用户模式启动SQL Server。打开SQL Server配置管理器，然后选择SQL Server实例的服务。右键单击并单击“ 属性”选项。

After adding the startup parameter, click on the Apply button and then the OK button in the warning message window.

添加启动参数后，在警告消息窗口中单击“ 应用”按钮，然后单击“ 确定”按钮。

Restart the SQL Server service to start SQL Server in single user mode. When the SQL Server is started in single user mode, all the users who are a member of the host’s local administrator group can connect to SQL Server instance and they will gain the privileges of server level role sysadmin which helps us to recover SA password.

重新启动SQL Server服务以单用户模式启动SQL Server。当以单用户模式启动SQL Server时，作为主机本地管理员组成员的所有用户都可以连接到SQL Server实例，他们将获得服务器级角色sysadmin的特权，这有助于我们恢复SA密码。

So, if you are a member of the local administrator group, you can connect to SQL Server instance using SQLCMD or SQL Server Management Studio. In this case, I am using SQLCMD.

因此，如果您是本地管理员组的成员，则可以使用SQLCMD或SQL Server Management Studio连接到SQL Server实例。在这种情况下，我正在使用SQLCMD。

Launch the Command Prompt and connect to SQL Server using SQLCMD. You would be able to successfully connect to the SQL Server instance.

启动命令提示符，并使用SQLCMD连接到SQL Server。您将能够成功连接到SQL Server实例。

Now, if you know the password of “sa” login, just enable the “sa” login and start SQL Server in multi-user mode, connect to SQL Server instance using “sa” login and create whatever logins you need for your application to run.

现在，如果您知道“ sa”登录名的密码，只需启用“ sa”登录名并以多用户模式启动SQL Server，然后使用“ sa”登录名连接到SQL Server实例并创建应用程序所需的任何登录名即可跑。

Please refer to the below T-SQL script to enable “sa” login.

请参考下面的T-SQL脚本以启用“ sa”登录。

ALTER LOGIN sa enable
GO

If you do not know the “sa” login password, create a SQL server login using the below T-SQL script.

如果您不知道“ sa”登录密码，请使用以下T-SQL脚本创建SQL Server登录。

CREATE LOGIN NewSA WITH PASSWORD = 'Password@1234';

Add the login NewSA to the server level role sysadmin. Please use the following T-SQL script to add the SQL Server login to the server level role sysadmin.

将登录名NewSA添加到服务器级别角色sysadmin。请使用以下T-SQL脚本将SQL Server登录名添加到服务器级别角色sysadmin。

ALTER SERVER ROLE sysadmin ADD MEMBER NewSA 
GO

Remove the startup parameter -m or -f that is added and restart the SQL Server services. Now, SQL Server instance is started in the multi-user mode and has the login that you created above. Please refer to the below image that shows the connection is established using NewSA login which is created above.

删除添加的启动参数-m或-f ，然后重新启动SQL Server服务。现在，SQL Server实例以多用户模式启动，并具有您在上面创建的登录名。请参考下图，该图显示了使用上面创建的NewSA登录名建立的连接。

In some cases, you may have not enabled mixed-mode authentication (i.e. SQL Server and Windows authentication mode). In this situation even though we create a SQL Server login, it does not allow us to log in to SQL Server and throws login failed error.

在某些情况下，您可能尚未启用混合模式身份验证（即SQL Server和Windows身份验证模式）。在这种情况下，即使我们创建了SQL Server登录名，也不允许我们登录SQL Server并引发登录失败错误。

Create a login from Windows account. Execute the following T-SQL script to create a Windows authentication login in SQL Server.

从Windows帐户创建登录名。执行以下T-SQL脚本在SQL Server中创建Windows身份验证登录。

CREATE LOGIN [WIN-UA7IGFIKF25\rangach] FROM WINDOWS
GO

Execute the following T-SQL script to add Windows authentication login to server level role sysadmin:

执行以下T-SQL脚本，将Windows身份验证登录名添加到服务器级别角色sysadmin：

ALTER SERVER ROLE sysadmin ADD MEMBER [WIN-UA7IGFIKF25\rangach]
GO

Replace the computer name and the login name with yours.

用您的计算机名和登录名替换。

Remove the startup parameter and restart the SQL Server service. Log in to the computer with the same user as above.

删除启动参数，然后重新启动SQL Server服务。使用与上述相同的用户身份登录到计算机。

Open SQL Server Management Studio and connect to the SQL Server instance using Windows authentication.

打开SQL Server Management Studio并使用Windows身份验证连接到SQL Server实例。

In case you want to create a login from Windows group, execute the following T-SQL script.

如果要从Windows组创建登录名，请执行以下T-SQL脚本。

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS
GO
ALTER SERVER ROLE sysadmin ADD MEMBER [BUILTIN\Administrators]
GO

结论 (Conclusion)

In this article, we explored how to gain access to SQL server instance or recover SA password when you forget it by starting SQL Server in single user mode. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们探讨了如何通过以单用户模式启动SQL Server来忘记密码而获得对SQL Server实例的访问或恢复SA密码的方法。如果您有任何疑问，请随时在下面的评论部分中提问。