Hello Reader,
I am sharing a quick script to disable all non sysadmin logins for SQL Server. You can not disable windows groups so I tweaked this script to add Deny Connect for Windows Groups.
This script is handy if you are also seeing the error below :
Msg 15151, Level 16, State 1, Line 2 Cannot alter the login 'XXX\XXXXXX-XXX', because it does not exist or you do not have permission.
USE master GO SELECT case p.type_desc when 'WINDOWS_GROUP' then 'DENY CONNECT SQL TO [' + p.name + '] CASCADE;' else 'ALTER LOGIN [' + p.name + '] DISABLE;' end , p.name AS [loginname] , p.type , p.type_desc , p.is_disabled, CONVERT(VARCHAR(10),p.create_date ,101) AS [created], CONVERT(VARCHAR(10),p.modify_date , 101) AS [update], s.sysadmin, s.setupadmin, s.bulkadmin,s.dbcreator,s.dbcreator,s.diskadmin, s.processadmin,s.setupadmin,s.serveradmin FROM sys.server_principals p JOIN sys.syslogins s ON p.sid = s.sid WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP') and p.name NOT LIKE '##%' AND (s.sysadmin <> 1 AND s.setupadmin <> 1 AND s.bulkadmin<> 1 AND s.dbcreator<> 1 AND s.diskadmin<> 1 AND s.processadmin<> 1 AND s.securityadmin<> 1 AND s.setupadmin<>1 AND s.serveradmin<> 1 ) GO