Invalid column name 'dbid'?

Hi,
I modified sp_helplogins a bit and made it just return the results about
one certain database (In this case, dbid is 30). So I added the condition
'l.dbid=30' to the following statement. But running the modified
sp_helplogins returned error "Invalid column name 'dbid'".
My master database does not have a table called syslogins, instead the table
is sysxlogins. But the original sp_helplogins which uses syslogins table
runs fine. I'm confused. What did I do wrong? Appreciate any help.
-- Add the User info to holding table.
select @.exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u.name
,''User''
from
' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
+
' UNION
Select
N' + quotename(@.c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
l.dbid = 30 and u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @.LoginNamePattern
when null then ''
else ' and ( l.name = N' +
quotename(@.LoginNamePattern ,
'''') + '
or l.loginname = N' +
quotename(@.LoginNamePattern , '''') + ')'
end
EXECUTE(@.exec_stmt)
end --loop 10
--
Thanks,
BingAfter looking more, looks like syslogins is a view on the sysxlogins table.
'dbid' is not a field in sysloigns. Hmmm..
"bing" wrote:

> Hi,
> I modified sp_helplogins a bit and made it just return the results about
> one certain database (In this case, dbid is 30). So I added the condition
> 'l.dbid=30' to the following statement. But running the modified
> sp_helplogins returned error "Invalid column name 'dbid'".
> My master database does not have a table called syslogins, instead the tab
le
> is sysxlogins. But the original sp_helplogins which uses syslogins table
> runs fine. I'm confused. What did I do wrong? Appreciate any help.
> -- Add the User info to holding table.
> select @.exec_stmt = '
> INSERT #tb1_UA
> (
> DBName
> ,LoginName
> ,UserName
> ,UserOrAlias
> )
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u.name
> ,''User''
> from
> ' + quotename(@.c10DBName, '[') + '.dbo.sysusers u
> ,master.dbo.syslogins l
> where
> l.dbid = 30 AND u.sid = l.sid AND isaliased=0' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> +
> ' UNION
> Select
> N' + quotename(@.c10DBName, '''') + '
> ,l.loginname
> ,u2.name
> ,''MemberOf''
> from
> ' + quotename(@.c10DBName, '[')+ '.dbo.sysmembers m
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u1
> ,' + quotename(@.c10DBName, '[')+ '.dbo.sysusers u2
> ,master.dbo.syslogins l
> where
> l.dbid = 30 and u1.sid = l.sid
> and m.memberuid = u1.uid
> and m.groupuid = u2.uid' +
> case @.LoginNamePattern
> when null then ''
> else ' and ( l.name = N' +
> quotename(@.LoginNamePattern ,
> '''') + '
> or l.loginname = N' +
> quotename(@.LoginNamePattern , '''') + ')'
> end
> EXECUTE(@.exec_stmt)
> end --loop 10
> --
> Thanks,
> Bing
>