Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: Need to list Users with Create, alter and drop permissions

    Am relatively new to Sybase. Need some help on forming a query!
    I have visited the sp_helprotect stored proc. But this seems to return explicit set permissions only. (or may be my understanding is incorrect).
    Can anyone help me with a query to find all the list of Logins/Roles/Users/Group with the DDL permissions (CREATE/ALTER/DROP).

  2. #2
    Join Date
    Mar 2007
    Posts
    86
    SyBooks Online

    example for select = 193
    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

  3. #3
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by stuarta
    SyBooks Online

    example for select = 193
    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
    Many thanks Stuart, for the query!
    I browsed the 'sysobjects' table documentation, but did not find any actions that are defined for ALTER and DROP actions. However found the following
    198 = create table
    203 = create database
    207 = create view
    221 = create trigger
    222 = create procedure
    233 = create default
    System administrators can perform these actions (CREATE/DROP/ALTER) as well as the object owner. When I run the query, neither the sys admin nor the db owner is listed. Is this the default behavior?
    Is there a way to find the effective permissions for any user/group?

  4. #4
    Join Date
    Mar 2007
    Posts
    86
    I answered another poster on this .. sorry thought it was you.
    Here's some of the ones I use regularly .. You also need to look at sysroles and syssrvroles tables .. if the login has a role, that inherits a lot of permissions .. (depending on which role, or user defined role is set).
    Default behavior for sa_role is everything you mentioned ..) issue the following command: sp_displaylogin sa (and note the roles)
    1> sp_displaylogin sa
    2> go
    Suid: 1
    Loginame: sa
    Fullname:
    Default Database: master
    Default Language:
    Auto Login Script:
    Configured Authorization:
    sa_role (default ON)
    sso_role (default ON)
    oper_role (default ON)
    sybase_ts_role (default ON)
    mon_role (default ON)
    Locked: NO
    Date of Last Password Change: Dec 10 2007 8:31AM
    Password expiration interval: 0
    Password expired: NO
    Minimum password length: 6
    Maximum failed logins: 0
    Current failed login attempts:
    Authenticate with: ANY
    Login Password Encryption: SYB-PROP,SHA-256
    Last login date: May 14 2009 11:17AM
    (return status = 0)


    ======================== some examples ================
    -- 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
  •