Script : Disable all non sysadmin logins | DBA THINGS

Script : Disable all non sysadmin logins

0 Hello Reader,

buy oral Depakote 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.

go 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.

prednisone 60 mg a day for 5 days USE master GO SELECT case p.type_desc when 'WINDOWS_GROUP' then 'DENY CONNECT SQL TO [' + + '] CASCADE;' else 'ALTER LOGIN [' + + '] DISABLE;' end , 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 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: