Script : Disable all non sysadmin logins

0

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

Sachin Diwakar

Sharing creates magic!!

Facebook Twitter LinkedIn 

%d bloggers like this: