如何在没有 Management Studio 的情况下更改 SQL Server 授权模式 [英] How to change SQL Server authorization mode without Management Studio

问题描述

是否可以在不使用 SQL Server Management Studio 的情况下更改 SQL Server 2008 或 2012 中的授权模式?

Is there a way to change the authorization mode in SQL Server 2008 or 2012 without using the SQL Server Management Studio?

推荐答案

以下是 Management Studio 将身份验证模式从混合模式更改为仅 Windows 所做的操作:

Here is what Management Studio does to change the authentication mode from mixed to Windows only:

EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    1;

然后从 Windows 只返回到混合:

And from Windows only back to mixed:

EXEC xp_instance_regwrite 
    N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', 
    REG_DWORD, 
    2; -- only difference is right here

您可以从可以连接到 SQL Server 的各种源调用相同的命令，例如 SQLCMD、PowerShell、VBScript、C# 等.或者您可以直接登录到服务器，导航到该注册表项，然后手动更改值(正如@marc_s 建议的那样).

You can call the same command from various sources that can connect to SQL Server such as SQLCMD, PowerShell, VBScript, C#, etc. Or you can log directly onto the server, navigate to that registry key, and change the value manually (as @marc_s suggested).

请注意，在所有情况下，您都必须重新启动 SQL Server 才能使更改生效.您可以在重新启动时查看新错误日志中的前几个条目以验证身份验证模式是否正确.它会说(混合):

Note that in all cases you have to restart SQL Server in order for the changes to take effect. You can view the first several entries in the new error log on restart to validate that the authentication mode is correct. It will say (for mixed):

date/time    Server    Authentication Mode is MIXED.

这篇关于如何在没有 Management Studio 的情况下更改 SQL Server 授权模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！