April 1, 2009 at 8:20 am
I am getting some unexpected results when I run the following queries. The counts are fine and the 'in' is fine. I expect 54 records from the 'not in' query, but I am getting 0. Any ideas what is happening? Your help is greatly appreciated.
select count(*) from sys.server_principals
69
select count(*) from [Database_Name].sys.database_principals
33
select *
from master.sys.server_principals
where sid in (select sid from [Database_Name].sys.database_principals)
15
select *
from master.sys.server_principals
where sid in (select sid from [Database_Name].sys.database_principals)
0 (I expect 69-15 = 54 rows).
April 1, 2009 at 11:55 am
lwolfe (4/1/2009)
select count(*) from sys.server_principals69
select count(*) from [Database_Name].sys.database_principals
33
select *
from master.sys.server_principals
where sid in (select sid from [Database_Name].sys.database_principals)
15
select *
from master.sys.server_principals
where sid in (select sid from [Database_Name].sys.database_principals)
0 (I expect 69-15 = 54 rows).
Did you get the right query in the last one? Looks same as your 3rd one;
Did you mean:
SELECT *
FROM sys.server_principals
WHERE sid NOT IN (...)?
I would expect number to be equal or higher not less ... that is there might be users in DATABASE that don't exist in Server because of orphan users and such.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 1, 2009 at 12:35 pm
Yes - the second who should read not in. I know there are logins that are not users of that database. I would like to get the list.
April 1, 2009 at 1:04 pm
I think that you are getting bitten by NULL logic.
This query:
select *
from master.sys.server_principals
where sid NOT in (select sid from [Database_Name].sys.database_principals)
will not work if there are any NULLs in the SID column of sys.database_principals. What you want to do is to make sure that no NULLs are returned by that subquery:
select *
from master.sys.server_principals
where sid in (select sid from [Database_Name].sys.database_principals
Where sid is not NULL)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply