Change SQL Server authentication mode using script

Question

How can I change the SQL server authentication mode by just a script?

I want a script which I can execute in the query window and change the authentication to the mixed-mode using it.

How can I do it?

Answer 1

For Windows only mode:

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

Use '2' instead of '1' for the mixed-mode authentication.

You will need to restart SQL Server after changing this setting. You can not able to do that from T-SQL. Using the command prompt(cmd), it is something like net stop mssqlserver then net start mssqlserver.

Want to become an expert in SQL? Join the SQL course fast!