How to check if a role exists in Sybase

Question

Anyone know how I can check if a role already exist in a Sybase database ... i have the name of the role

There is a sysroles table but no name column!

select * from sysroles

Answer 1

Have a look at proc_role("role_name") (now depreciated to has_role) or role_id("role_name").

Answer 2

1. SELECT HAS_ROLE("role_name", 1) tells you if the user executing the code segment has the role, not if the role exists. Of course you have to be aware of the db context.

2. If you are going to use the sys tables, you need to look at the doco (PDF, not online, which can be downloaded), the table diagram (shows relations), and get used to them. The tables are almost completely normalised.

• sysroles exists in each user db, it contains one row per role in the db. It does not need "role_name".
• master..syssrvroles contains one row per role in the server; you will find the role_name there.

    -- Check if role exists in server
    SELECT [RolesInSvr] = svr.name
        FROM  master..syssrvroles svr
        WHERE name = "role_name"  
    -- Check if role exists in db
    SELECT [RolesInDb] = svr.name
        FROM  master..syssrvroles svr,
              sysroles            db
        WHERE svr.srid = db.id
        AND   svr.name = "role_name"  
    -- List roles in db
    SELECT [RolesInDb] = svr.name,
               [Locked]    = CASE svr.status & 2 
                    WHEN 2 THEN "Locked" 
                    ELSE         CHAR(0)
                    END
               [Expired]   = CASE svr.status & 4 
                    WHEN 4 THEN "Expired" 
                    ELSE         CHAR(0)
                    END
            FROM  master..syssrvroles svr,
                  sysroles            db
            WHERE svr.srid = db.id