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

2014-10-13



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



Newer Older