Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    27

    Unanswered: finding user permissions for given user

    Can someone provide me the general SQL to query the Sybase system tables to determine all of the tables a particular user has both decrypt and select permissions too in a given user database?

    Thanks,

    Patrick Quinn

    Carlson Wagonlit Travel Database Admin

  2. #2
    Join Date
    May 2009
    Posts
    8
    try sp_helprotect!

  3. #3
    Join Date
    Mar 2007
    Posts
    86
    -- generate sp_addgroup
    select "EXEC sp_addgroup " +name from sysusers where suid=-2 and name not like "%role%" and name <> "public"
    -- generate sp_adduser
    select "EXEC sp_adduser " +"'" +name +"'" +"," +"'" +name +"'" +"," +"'" +user_name(gid) +"'" from sysusers where suid>3
    -- generate grants
    select 'GRANT select on ' +object_name(sp.id) +' TO ' +user_name(sp.uid)
    from sysprotects sp, sysobjects so
    where sp.id = so.id and so.type in('U','V') and sp.action = 193
    order by so.type,so.name
    select 'GRANT insert on ' +object_name(sp.id) +' TO ' +user_name(sp.uid)
    from sysprotects sp, sysobjects so
    where sp.id = so.id and so.type in('U','V') and sp.action = 195
    select 'GRANT delete on ' +object_name(sp.id) +' TO ' +user_name(sp.uid)
    from sysprotects sp, sysobjects so
    where sp.id = so.id and so.type in('U','V') and sp.action = 196
    select 'GRANT update on ' +object_name(sp.id) +' TO ' +user_name(sp.uid)
    from sysprotects sp, sysobjects so
    where sp.id = so.id and so.type in('U','V') and sp.action = 197
    select 'GRANT exec on ' +object_name(sp.id) +' TO ' +user_name(sp.uid)
    from sysprotects sp, sysobjects so
    where sp.id = so.id and so.type = 'P' and sp.action = 224

    I didn't list grant 'grant' .. forget the sp_action for that one ..
    Regards,
    s-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •