Script : Disable all non sysadmin logins


enter site 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.

il 100 de bonus su iq optiom vuole dire che si meto 200 euro ho 400 This script is handy if you are also seeing the error below :

source link 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.

click 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

source link

Sachin Diwakar

sixty second trades indicator Sharing creates magic!!

click here Facebook Twitter LinkedIn 

follow site

follow link

%d bloggers like this: