[T-SQL] Recursive T-SQL to do “DENY IMPERSONATE ON LOGIN”

The situation depends on what your SQL environment are? For my case, I need to “DENY IMPERSONATE ON LOGIN::” to most of DBA logins and general Logins except real necessary DBAs. But I don’t like to COPY/PASTE command for lots users (if your server has over 20 accounts), so I created a script to do this.

It’s an inefficient T-SQL script due to my desperate boss wants to see the results for all SQL instances… if you can modify it for me, I will be very appreciated.

*Note : you CANNOT DENY Impersonate to fixed server role, SA, sysadmin group

For my desperate BOSS:

;WITH CTE_Query1

      AS (

      select '' as [T-SQL], * from sys.server_principals usr1

      where

      (usr1.[name] not like 'NT %' and usr1.[name] not like N'##%')

      AND usr1.[type] <> 'R'

      (AND usr1.principal_id > 1 and usr1.principal_id <= 10) --no fixed Server Roles

      )

      SELECT

      CASE

           WHEN CTE_query1.[name] = sys.server_principals.[name]

                    THEN 'GRANT IMPERSONATE ON LOGIN::' + QUOTENAME(CTE_query1.[name], '[]') + ' TO ' + QUOTENAME(sys.server_principals.[name],'[]')

             ELSE  'DENY IMPERSONATE ON LOGIN::' + QUOTENAME(CTE_query1.[name], '[]') + ' TO ' + QUOTENAME(sys.server_principals.[name],'[]')

      END  

              as [T-SQL2], sys.server_principals.*

      ,ROW_NUMBER() OVER(

      PARTITION BY CTE_query1.[name]

      ORDER BY sys.server_principals.[NAME]

      )

      FROM sys.server_principals

      CROSS JOIN CTE_Query1
      WHERE (sys.server_principals.[name] not like 'NT %' and sys.server_principals.[name] not like N'##%')
 --Optional if you don't want put your DBA team
 --AND  sys.server_principals.[name] not in ('xxx')

      ORDER BY sys.server_principals.[name] DESC