In this post, I am sharing a T-SQL script to identify a current SQL Server Authentication Mode.
You can use SERVERPROPERTY to find this details of SQL Server.
Why is this important?
In my previous organization, we have 23 different SQL Server Instances, and we set policies to allow System admin using only Windows Authentication mode.
The reason is security, and Windows Active Directory is more secure than SQL Server database user authentication.
Simply, we want to check the authentication mode of all the SQL Server Instances, so I found below script which I am sharing here.
WHEN 0 THEN 'Mixed Mode - Authentication Mode (Both SQL Server and Windows Authentication)'
WHEN 1 THEN 'Only Windows Authentication Mode.'
END AS SQL_Server_Authentication_Mode