Chapter 3 DBMS Reference Guide

SqlPermQuery

SQL Query to reverse engineer permissions granted on users.

Example

In Sybase Adaptive Server Enterprise 12.5:

{USER ID, PRIVILEGE ...}

select u1.name grantee, 
case 
    when (s.action = 198) then 'CREATE TABLE'
    when (s.action = 203) then 'CREATE DATABASE'
    when (s.action = 207) then 'CREATE VIEW'
    when (s.action = 221) then 'CREATE TRIGGER'
    when (s.action = 222) then 'CREATE PROCEDURE'
    when (s.action = 233) then 'CREATE DEFAULT'
    when (s.action = 236) then 'CREATE RULE'
end +
case
    when (s.protecttype = 0) then '+'
    when (s.protecttype = 1) then ''
    when (s.protecttype = 2) then '-'
end
|| ','
from sysprotects s, sysusers u1
where u1.uid = s.uid
order by 1

Copyright (C) 2005. Sybase Inc. All rights reserved.