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

    Unanswered: database schema permissions query

    Greetings all,

    A second question, for all database schema, what is the basic query to retrieve all database objects that has any sort of permission on a given Sybase login id?

    Patrick Quinn
    Carlson Wagonlit Travel Database Administrator

  2. #2
    Join Date
    Mar 2007
    Posts
    86
    Quote Originally Posted by powerdude
    Greetings all,

    A second question, for all database schema, what is the basic query to retrieve all database objects that has any sort of permission on a given Sybase login id?

    Patrick Quinn
    Carlson Wagonlit Travel Database Administrator
    1. join sysobjects with sysprotects using id
    2. filter for sysobjects.type
    3. test sysprotects.action
    == 193 = select perms
    == 195 = insert perms
    == 196 = delete perms
    == 197 = update perms
    == 224 = exec perms (procs)

    There is probably a more complete list published somewhere, but these are the ones I can think of at the moment. The method is the same for all.

  3. #3
    Join Date
    Mar 2007
    Posts
    86
    Quote Originally Posted by stuarta
    1. join sysobjects with sysprotects using id
    2. filter for sysobjects.type
    3. test sysprotects.action
    == 193 = select perms
    == 195 = insert perms
    == 196 = delete perms
    == 197 = update perms
    == 224 = exec perms (procs)

    There is probably a more complete list published somewhere, but these are the ones I can think of at the moment. The method is the same for all.
    PART II --- to link to login

    use master..syslogins to obtain dbname
    check sysusers table in "dbname" for login id use (suid)
    use the uid and suid cols to resolve these .. and filter the sysobjects with the sysusers.uid ..

    you may have to check every database on the server, as dbname is the default db for the login .. the user may be aliased as well.. so a little thought will be required to address every contingency.

Posting Permissions

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