Extract "GroupName" from "sp_helpuser"

Hi all,

I want to create a stored procedure which will extract the "GroupName"
from the record returned by "sp_helpuser". In order to do this I need
to execute "sp_helpuser" which returns the entire record. I want to
just extract the "GroupName" from the record and return it to my
application. How do I go about this?

Thanks in advance,

AlvinAlvin Sebastian (asebastian@.cmri.usyd.edu.au) writes:
> I want to create a stored procedure which will extract the "GroupName"
> from the record returned by "sp_helpuser". In order to do this I need
> to execute "sp_helpuser" which returns the entire record. I want to
> just extract the "GroupName" from the record and return it to my
> application. How do I go about this?

Either you access sysusers directly, you can use the INSERT EXEC construct:

INSERT #temp (...)
EXEC sp_helpuser

You need to create #temp so that it agrees with the output from sp_helpuser.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

By the way, I forgot to mention in the first post that I'm only
interested in the "GroupName" of the currently logged-on user so the
stored procedure will be returning a single string value only and not
a table. How should the stored procedure return this single value from
the record returned by "sp_helpuser"?

Alvin

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93BF646E19EF7Yazorman@.127.0.0.1>...
> Alvin Sebastian (asebastian@.cmri.usyd.edu.au) writes:
> > I want to create a stored procedure which will extract the "GroupName"
> > from the record returned by "sp_helpuser". In order to do this I need
> > to execute "sp_helpuser" which returns the entire record. I want to
> > just extract the "GroupName" from the record and return it to my
> > application. How do I go about this?
> Either you access sysusers directly, you can use the INSERT EXEC construct:
> INSERT #temp (...)
> EXEC sp_helpuser
> You need to create #temp so that it agrees with the output from sp_helpuser.|||Alvin Sebastian (asebastian@.cmri.usyd.edu.au) writes:
> By the way, I forgot to mention in the first post that I'm only
> interested in the "GroupName" of the currently logged-on user so the
> stored procedure will be returning a single string value only and not
> a table. How should the stored procedure return this single value from
> the record returned by "sp_helpuser"?

A one-row result set is still a table.

There is the OPENQUERY method as well.

See http://www.algonet.se/~sommar/share_data.html where I discuss both
methods.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I got it working now!