[maredman]

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

[perl]

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 !

[maredman]

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.

[perl]

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