I twisted my head around a select statement that wasn't returning what I wanted.  Eventually I focused in on the column list, that had column names with spaces, so it required using brackets [].  These brackets when mixed in the column list can lead to tsql parsing that is not expected.  In this case I mixed some columns with brackets and some without, ultimately leaving a comma off.

You'd expect the tsql to not compile, but it does and runs with no errors.  Once tsql encounters a column list with brackets, it parses based off the brackets and commas ignoring the additional field i wanted to include in the select list..

grrrr...

create table #testTable
    (tableid int identity(1,1) primary key,
        [login name] varchar(20),
        [is_password_validation] int,
        [is_password_expiration] int,
        [login type] varchar(20))

Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 1',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 2',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 3',0,0,'AD')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 4',1,1,'SQL')
Insert into #testTable ([login name],[is_password_validation],
    [is_password_expiration],[login type] ) values ('User 5',0,1,'SQL')

select * from #testTable -- good

select [login name],
    is_password_validation -- NOTICE THE MISSING COMMA
    [login type]
    From #testTable