Retrieve a list of db roles from within T-SQL????

[CxG]

How do you retrieve a list of db roles from within T-SQL???

I can't find a sys table that returns a list of roles. sysobjects
does not list roles as a object type.

I found sp_helprole, but I can't get the sp to return it's output into
a cursor or variable.

Help??

Regards,
CxG

[Allan Mitchell]

What about

Exec sp_helprole
--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"CxG" <ch***********@lmco.com> wrote in message
news:35************************@posting.google.com ...

How do you retrieve a list of db roles from within T-SQL???

I can't find a sys table that returns a list of roles. sysobjects
does not list roles as a object type.

I found sp_helprole, but I can't get the sp to return it's output into
a cursor or variable.

Help??

Regards,
CxG

[Allan Mitchell]

Sorry. Fingers faster than brain

If you look inside sp_helprole you can see the queries that this proc
executes

select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
from sysusers where issqlrole = 1 or isapprole = 1

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Allan Mitchell" <al***@no-spam.sqldts.com> wrote in message
news:40***********************@lovejoy.zen.co.uk.. .

What about

Exec sp_helprole
--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"CxG" <ch***********@lmco.com> wrote in message
news:35************************@posting.google.com ...

How do you retrieve a list of db roles from within T-SQL???

I can't find a sys table that returns a list of roles. sysobjects
does not list roles as a object type.

I found sp_helprole, but I can't get the sp to return it's output into
a cursor or variable.

Help??

Regards,
CxG

[Trevor Best]

CxG wrote:

How do you retrieve a list of db roles from within T-SQL???

I can't find a sys table that returns a list of roles. sysobjects
does not list roles as a object type.

I found sp_helprole, but I can't get the sp to return it's output into
a cursor or variable.

Help??

Regards,
CxG

From looking at what sp_helprole does...

select 'RoleName' = name, 'RoleId' = uid, 'IsAppRole' = isapprole
from sysusers where issqlrole = 1 or isapprole = 1

--
Error reading sig - A)bort R)etry I)nfluence with large hammer