How to get all the users with sa_role or sso_role ?

[Rk]

On ASE 12.5.1.
How can I get all the users with sa_role or sso_role ? Tried sp_displaylogin
and sp_dosplayroles, But these only gives for a particular user, But I need
the list of users with sa_role.

Thanks
RK

[Rk]

The attachment was not downloadable, Could you attache the text in the reply
(instead of a file).
<wk> wrote in message news:433176f7.560...@sybase.com...

> I have attached a stored procedure to display all the roles
> and assignments. It also will display roles granted to
> roles.
> >
> >
>

[Mark A. Parsons]

use master
go
select suser_name(slr.suid) as 'login',
ssr.name as 'role'
from sysloginroles slr,
syssrvroles ssr
where ssr.name in ('sa_role','sso_role')
and ssr.srid = slr.srid
order by 1,2
go

[Tartampion]

I am interested in your stored proc, but I can see the
attached file(using a browser), could you put the stored
proc in the body of of the text, please? or perhaps you
could tell me how to see the attached file.

Thanks
tartampion

> > On ASE 12.5.1.
> > How can I get all the users with sa_role or sso_role ?
> > Tried sp_displaylogin and sp_dosplayroles, But these
> > only gives for a particular user, But I need the list of
> > users with sa_role.
> >
> > Thanks
> > RK
>
> I have attached a stored procedure to display all the
> roles and assignments. It also will display roles granted
> to roles.
> >
> >
>
>

> [sp__helprole.sql]
> create procedure sp__helprole
> as
> /*
> ==========================================================
> ============ */ /*
> */ /* Who Procedure
> Version Date Comments */ /* ---
> ------- ------ ---- --------
> */ /* W.Kraatz sp__helprole v01 rel01 02/12/99
> */ /*
> */ /*
> ==========================================================
> ============ */ /* Proprietary and Confidential
> Information of ** **
> Computer Sciences Corp. **
> ** **
> Protected by the Copyright Laws as an Unpublished Work
> ** ** All rights reserved.
> ** **
> */
> set nocount on SELECT trole=convert(char(20), sr.name),
> LOGID=convert(char(11), l.name),
> tname=convert(char(25), l.fullname),
> ActAtLogn = Case
> when sr.name in ( 'navigator_role',
> 'sybase_ts_role', 'sa_role',
> 'oper_role', 'replication_role',
> 'sso_role' )
> then 'Y'
> when lr.status = 0
> then 'N'
> when lr.status = 1
> then 'Y'
> else '-'
> end
> , PswdRqrd= Case
> when datalength(sr.password) > 0
> then 'Y'
> else 'N'
> end
> into #tmprole
> FROM master..sysloginroles lr,
> master..syssrvroles sr,
> master..syslogins l
> WHERE ( lr.suid *= l.suid )
> and ( lr.srid =* sr.srid )
> update #tmprole set LOGID = ' ' , tname = 'Not
> assigned to a user'
> where LOGID is null
>
> select 'Role'=trole, LOGID, 'Name'=tname, ActAtLogn,
> PswdRqrd from #tmprole
> ORDER BY trole , LOGID
>
> print ' '
> select 'Roles granted to Roles' = 'grant role ' +
> role_name(object_info1) + ' to ' + role_name(object)
> from master..sysattributes,
> master..syssrvroles sr
> where class = 8 and attribute = 2 and sr.srid =
> object_info1
>
> go
> if exists (select * from sysobjects where name
> ='sp__helprole')
> begin
> declare @msg varchar(200)
> select @msg="stored procedure sp__helprole was
> created in the "+db_name()+" db."
> print @msg
> end
> go
> grant execute on sp__helprole to public
> go
> [Attachment: sp__helprole.sql]

[wk]

> I am interested in your stored proc, but I can see the
> attached file(using a browser), could you put the stored
> proc in the body of of the text, please? or perhaps you
> could tell me how to see the attached file.
>
> Thanks
> tartampion
>

I was out of the office for the week. Here is the proc.

[wk]

I was out of the office for a week. Here is the proc.