Join the Result of xp_logininfo with a table

Hello group!

i have a table "group_code" wich relates the names of
nt-(domain)-groups to codes. now i want use the stored procedure
xp_logininfo (asking for the group-membership of the current user) to
join the result to "group_code". then i must use the new result (the
code) to join against other tables.

i know now, that i cant join results of SPs against tables. may be that
a UDF with a table result is the correct approach. but i have no idea
how to wrap the xp_logininfo in a UDF.

a other way can be to do in a UDF the same thing like the xp_logininfo.
then this UDF should deliver at least the nt-(domain)-groups in wich
the current-user is a member.

Is there anybody who can give me the code for that?
many thanx in advance.

Karl(Karl.Auer@.gmail.com) writes:
> i have a table "group_code" wich relates the names of
> nt-(domain)-groups to codes. now i want use the stored procedure
> xp_logininfo (asking for the group-membership of the current user) to
> join the result to "group_code". then i must use the new result (the
> code) to join against other tables.
> i know now, that i cant join results of SPs against tables. may be that
> a UDF with a table result is the correct approach. but i have no idea
> how to wrap the xp_logininfo in a UDF.
> a other way can be to do in a UDF the same thing like the xp_logininfo.
> then this UDF should deliver at least the nt-(domain)-groups in wich
> the current-user is a member.

I don't think you can do this with a UDF. You can do:

CREATE TABLE #temp (...)
INSERT #temp (...)
EXEC master..xp_logininfo

But you cannot do this in a UDF, since you cannot create temp
tables in a UDF, and you cannot do INSERT EXEC on a table variable
if memory serves.

As for how #temp in the example should be created, see the definition
of the columns for xp_logininfo in Books Online.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp