2007 - Admin Reports

[Roland]

I need to produce a report which shows what resource has been assigned to a
Group. Is there an easy way to do this?

 

[Jonathan Sofer [MVP]]

If you are asking for a report that shows what users are part of what
security groups stored under "PWA>Server Settings>Security>Manage Groups",
then the only way I know to do this is using SQL Reporting Services and
reading this data from the Published DB.

Disclaimer, reading from the published DB is not supported by Microsoft,
only reading from the reporting DB is supported. Regardless, this data only
exists in the published DB and here is a query I have used in the past to
get the group and user relationships:

SELECT MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, MSP_WEB_RESOURCES.RES_NAME
FROM MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
MSP_WEB_SECURITY_GROUPS ON
MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID =
MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID =
MSP_WEB_RESOURCES.WRES_GUID
ORDER BY MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, MSP_WEB_RESOURCES.RES_NAME

Hope this helps,

Jonathan Sofer

 

[Roland]

This approach worked well. Thank you!

If you are asking for a report that shows what users are part of what
security groups stored under "PWA>Server Settings>Security>Manage Groups",
then the only way I know to do this is using SQL Reporting Services and
reading this data from the Published DB.

Disclaimer, reading from the published DB is not supported by Microsoft,
only reading from the reporting DB is supported. Regardless, this data only
exists in the published DB and here is a query I have used in the past to
get the group and user relationships:

SELECT MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, MSP_WEB_RESOURCES.RES_NAME
FROM MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
MSP_WEB_SECURITY_GROUPS ON
MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID =
MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID =
MSP_WEB_RESOURCES.WRES_GUID
ORDER BY MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, MSP_WEB_RESOURCES.RES_NAME

Hope this helps,

Jonathan Sofer

 

[Hernandezz]

If you are asking for a report that shows what users are part of what
security groups stored under "PWA>Server Settings>Security>Manage Groups",
then the only way I know to do this is using SQL Reporting Services and
reading this data from the Published DB.

Disclaimer, reading from the published DB is not supported by Microsoft,
only reading from the reporting DB is supported.  Regardless, this dataonly
exists in the published DB and here is a query I have used in the past to
get the group and user relationships:

SELECT MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, MSP_WEB_RESOURCES.RES_NAME
FROM  MSP_WEB_SECURITY_GROUP_MEMBERS INNER JOIN
MSP_WEB_SECURITY_GROUPS ON
MSP_WEB_SECURITY_GROUP_MEMBERS.WSEC_GRP_GUID =
MSP_WEB_SECURITY_GROUPS.WSEC_GRP_GUID INNER JOIN
MSP_WEB_RESOURCES ON MSP_WEB_SECURITY_GROUP_MEMBERS.WRES_GUID =
MSP_WEB_RESOURCES.WRES_GUID
ORDER BY MSP_WEB_SECURITY_GROUPS.WSEC_GRP_NAME, MSP_WEB_RESOURCES.RES_NAME

Hope this helps,

Jonathan Sofer

thanks, very useful!!!

do you have more query like these? send me please...