Sql-server – Modification in the SQL script

sql serversql server 2014t-sql

I want sysadmin logins which are not listed in the exceptionssysadmin table.
I'm receiving empty columns with the below script.
can someone suggest where am i going wrong ?

The below is the script

SELECT DISTINCT p.name AS [loginname]
    ,p.type_desc
    ,p.is_disabled
    ,s.sysadmin
    ,CONVERT(VARCHAR(10), p.create_date, 101) AS [created]
    ,CONVERT(VARCHAR(10), p.modify_date, 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
CROSS JOIN test..ExceptionsSysadmin ES
WHERE ES.AccountName  NOT IN (
        SELECT AccountName
        FROM ExceptionsSysAdmin
        )
    AND p.type_desc IN (
        'SQL_LOGIN'
        ,'WINDOWS_LOGIN'
        ,'WINDOWS_GROUP'
        )
    -- Logins that are not process logins
    AND p.name NOT LIKE '##%'
    -- Logins that are sysadmins or have GRANT CONTROL SERVER
    AND (
        s.sysadmin = 1
        OR sp.permission_name = 'CONTROL SERVER'
        )
ORDER BY p.name

Best Answer

I want sysadmin logins which are not listed in the exceptionssysadmin table

The cross join & IN() look like they are not needed.

You could use NOT EXISTS() for checks if a value is not in a certain table

This could be a solution:

SELECT DISTINCT p.name AS [loginname]
    ,p.type_desc
    ,p.is_disabled
    ,s.sysadmin
    ,CONVERT(VARCHAR(10), p.create_date, 101) AS [created]
    ,CONVERT(VARCHAR(10), p.modify_date, 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE NOT EXISTS 
(
        SELECT AccountName
        FROM Test..ExceptionsSysAdmin
        WHERE p.[name] = AccountName 
 )
    AND p.type_desc IN (
        'SQL_LOGIN'
        ,'WINDOWS_LOGIN'
        ,'WINDOWS_GROUP'
        )
    -- Logins that are not process logins
    AND p.name NOT LIKE '##%'
    -- Logins that are sysadmins or have GRANT CONTROL SERVER
    AND (
        s.sysadmin = 1
        OR sp.permission_name = 'CONTROL SERVER'
        )
ORDER BY p.name;

Tested with:

CREATE TABLE 
ExceptionsSysAdmin(accountname varchar(255))

INSERT INTO ExceptionsSysAdmin(accountname)
VALUES('NT SERVICE\SQLSERVERAGENT')

And this login was excluded as a result