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.

1 comment:

  1. Thankfully, it stopped being a problem for me to deal with this database management system, and I can tell you that not so long ago, I finally managed to discover online waiver software to optimize my workflow. So now I don't have to deal with everything myself and spend a lot of time solving all the problems manually.

    ReplyDelete