How to change SQL Server Authentication Mode by using TSQL Query

SQL Server Authentication mode can be changed by using GUI.

Simply click on Server -> Properties -> Security.

There you can change the SQL Server Authentication mode to

1. Windows Authentication mode
2. SQL Server and Windows Authentication mode( Mixed Mode)

You can also use below scripts to check and change the SQL Server Authentication mode:

--Use this to Check the SQL Server Authentication Mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') 
WHEN 1 THEN 'Windows Authentication' 
WHEN 0 THEN 'Windows and SQL Server Authentication' 
END as [SQLServerAuthenticationMode]
------------------------------------------------------------------------------
--To Change to Windows Authentication

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO
------------------------------------------------------------------------------
--To change to Mixed Mode( SQL Server and Windows Authentication mode)

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

------------------------------------------------------------------------------
--Finally:
--Please note: After running one of the above query, you have to restart SQL Server and Agent Service.