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:
Server Name | SQL Login drop |
server1instance1 | user1 |
server1instance1 | user2 |
server1instance1 | user3 |
server2instance2 | user1 |
server2instance2 | user2 |
server2instance2 | user3 |
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:
Server Name | SQL Login drop command |
server1instance1 | USE [master];DROP LOGIN [user1]; |
server1instance1 | USE [master];DROP LOGIN [user2]; |
server1instance1 | USE [master];DROP LOGIN [user3]; |
server2instance2 | USE [master];DROP LOGIN [user1]; |
server2instance2 | USE [master];DROP LOGIN [user2]; |
server2instance2 | USE [master];DROP LOGIN [user3]; |
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.