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.

3 comments:

  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
  2. It seems to be quite complicated, and I can say that's the reason I prefer to work with low-code solutions.

    ReplyDelete
  3. I agree that working with low code solutions can be quite beneficial, but it can be a challenge to find anything decent. Personally, I managed to discover a great backend low code platform not so long ago, and I think you can check it out as well because it helped me improve the efficiency of my app pretty fast.

    ReplyDelete