SQL Server Tips: Find sql logins when “windows authentication only” is enabled

A customer asks me to find all sql logins enabled on servers where windows authentication only is enabled.

The goal is to clean sql logins on all servers through the CMS (central management server). In some cases, sql logins are created even if the authentication is set to “Windows authentication only mode”.

I think that this query is interesting and I will just share it with you.

DECLARE @value INT
USE [master]
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServer', N'LoginMode', @value = @value OUTPUT
IF @value=1 -- Windows authentication only
BEGIN
 SELECT name as  FROM sys.sql_logins WHERE is_disabled=0
END
ELSE 
PRINT 'mixte mode is used'

Results:

You obtain the list of all sql logins that you can delete or disable. I use the clause where is_disabled because in this case (Windows authentication only), the ‘sa’ account is disable after the installation like the other sql account beginning with ‘##’ like ##MS_PolicyEventProcessingLogin##.

In my case, I will drop all sql logins, then I add the Drop login command to my select:

DECLARE @value INT
USE [master]
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServer', N'LoginMode', @value = @value OUTPUT
IF @value=1 --Windows authentication only
BEGIN
 SELECT 'USE [master];DROP LOGIN ['+name+'];' as [SQL Login drop command] FROM sys.sql_logins WHERE is_disabled = 0
END
ELSE PRINT 'mixte mode is enabled'

Results:

Et voila! I can clean all servers… 😎

But be careful, this will not delete database-users if you have it linked to your SQL Logins.