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_Query1AS (select '' as [T-SQL], * from sys.server_principals usr1where(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)SELECTCASEWHEN 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],'[]')ENDas [T-SQL2], sys.server_principals.*,ROW_NUMBER() OVER(PARTITION BY CTE_query1.[name]ORDER BY sys.server_principals.[NAME])FROM sys.server_principalsCROSS 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
No comments
Post a Comment