How to check if a role exists in Sybase
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 n开发者_JS百科o name column!
select * from sysroles
Have a look at proc_role("role_name") (now depreciated to has_role) or role_id("role_name").
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.
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
精彩评论