Infrastructure at your Service

Stéphane Haby

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'Software\Microsoft\Microsoft SQL Server\MSSQLServer', 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
server1\instance1 user1
server1\instance1 user2
server1\instance1 user3
server2\instance2 user1
server2\instance2 user2
server2\instance2 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'Software\Microsoft\Microsoft SQL Server\MSSQLServer', 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
server1\instance1 USE [master];DROP LOGIN [user1];
server1\instance1 USE [master];DROP LOGIN [user2];
server1\instance1 USE [master];DROP LOGIN [user3];
server2\instance2 USE [master];DROP LOGIN [user1];
server2\instance2 USE [master];DROP LOGIN [user2];
server2\instance2 USE [master];DROP LOGIN [user3];

Et voila! I can clean all servers… 8-)

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

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager