How to change SQL Server Authentication Mode by using TSQL Query - SQL Server Script

SQL Server Authentication mode can be changed by using GUI. You have to click on Server and then go to Properties and then Security. From there you can change the SQL Server Authentication mode to

• Windows Authentication mode
• SQL Server and Windows Authentication mode( Mixed Mode)

You can also use below script to check and change the SQL Server Authentication mode.

--We can use  below Query 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

After running one of above query, you have to restart SQL Server and Agent Service.