Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: SQL for User and Group Permission Report

    Due to the new Sarbanes-Oxley control requirements, I need to produce a report showing all users in a database, what group they are in and what permissions are assigned to the group. Can someone help me out with the SQL to produce this output?
    I will be eternally grateful for any help you can provide.

    Thanks!
    MArk A. Redman
    The Southern Company

  2. #2
    Join Date
    Jun 2003
    Posts
    140
    Use sp_helpuser to get list of all the users and the group they belong to
    Use sp_helprotect "group name" to get all the database permission of that group

    Alternatively you can insert all the result of sp_helpuser to a temptable
    and sort it by group name then execute sp_helprotect for each group
    one by one

    hope this helps u,
    all the best !

  3. #3
    Join Date
    Jun 2004
    Posts
    2

    RE: How

    Thanks! That is great information. How can I insert the results of the sp_heluser command into a table?
    I realize that this is probably a very simple question but I cannot remember ever having tried this before.

    Thanks again,
    Mark.

  4. #4
    Join Date
    Jun 2003
    Posts
    140
    good question, but reply is simple , if you look at sp_helpuser proc
    data goes to #tmp table in all cases, all you have to do is to create dummy proc with the same contents of sp_helpuser , something like

    sp_helpuser_byme and replace #tmp table with permanent table inside
    and then in last line of your proc simply select all the rows from that table !!
    This will be your user and group report then you can take each group from
    this table and run sp_helprotect aginst that

Posting Permissions

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