T-sql comparing results of linked server query with local query

Question

I am trying to compare the results of a linked server query with a local query. What I am try to do is find out what Logins exist on one server but not the other. Just windows auth accounts is fine for now.

My current query is

Select name 
from [linkedServer].master.[sys].[server_principals]  
Where name not in ('Select name from sys.server_principals')

What I get back is the result of the linked server query only with the where ignored. How do I go about comparing the results of the two?

Answer 1

First, use not exists. Second, I think your single quotes are wrong:

select sp.name
from [linkedServer].master.[sys].[server_principals] sp
where not exists (select 1
                  from sys.server_principals sp2
                  where sp2.name = sp.name
                 );

I strongly recommend that you get in the habit of using not exists with a subquery rather than not in, because not in returns no row if any value from the subquery is NULL. That is usually not desirable behavior.