Skip to content

Show All Sites User Has Access to In SharePoint 2007

by on June 3, 2014

!Updated for 2010! See Below

Using OOTB Web Parts & SharePoint Designer

  • XML web services
  • SharePoint Designer

Add XML Web Service as a Data Source to Site Collection

  1. Open SharePoint Designer 2007
  2. Connect to site collection root
  3. If Data Source Library is not visible
    1. Menu bar > task panes menu > data source library
  4. In the Data Source Library > expand XML Web Services
  5. Click on connect to a web service  image
  6. In the Data source Properties popup box, type in the address to the search web service http://YOUR SITE COLLECTION HERE/_vti_bin/search.asmx?WSDL
  7. Click on “Connect Now”
  8. Change operation to QueryEx – this will return an actual Data Set, not a blobimage
  9. Click on the Modify button below Parameters
  10. In the popup enter your query xml – I have an example below (Watch quote marks when cutting and pasting from WordPress > OK
    <QueryPacket xmlns=’urn:Microsoft.Search.Query’><Query><SupportedFormats><Format revision=’1′>urn:Microsoft.Search.Response.Document:Document</Format></SupportedFormats><Context><QueryText language=’en-us’ type=’MSSQLFT’>select title, path, Description from scope() where (contentclass = ‘STS_Web’ or contentclass = ‘STS_Site’) and path not like ‘%ssp/admin%’ order by title</QueryText></Context><Range><StartAt>1</StartAt><Count>1000</Count></Range></Query></QueryPacket>
    • Will return everything, including docs, list items – limited to 1000 items
    • NOTE: for SharePoint 2010 it is very similar, but for some reason MS decided you would have to escape the characters in the query.
  11. In the new Query Service dropdown > select “Show Data”image
  12. Click on the new page icon image or you can go to a page that is already created > open page
  13. Select Title, Path, and description in Data Source Details (don’t include hyperlink if you do not want this to be visible in the display, we will set the link up later)image
  14. Click on the Insert Selected Fields dropdown (if you are in a viable location – ie. a page – the dropdown will not be greyed out) > Multiple Item Viewimage
  15. In the page, hover over a title and click on the “>” button > in the popup set Format As to Hyperlink > If you get a warning, click Yes image
  16. Set the Address dropdown to {Path} then you will have to reset the Text to Display to {TITLE}! It will automatically change to {PATH}! > Click OKimage
  17. You can format the dataset by clicking on the arrow to the top right – change filters, sorting, grouping, layout, etc.image
  18. Use additional styling and jQuery to really customize the look and feel if you would like – you can always change the view to source and edit the actual code on the page as well
  19. After a little fiddling and some class additions, I am happy with the basic format which will allow me styling flexibilityimage

To Export As A Web Part

  1. Click on the WebPartPages:DataFormWebPart to make it activeimage
  2. File > Export > Save Web Part To > Site Galleryimage
  3. Change title, add description and click okimage
  4. Now go to a page to which you can add web parts > click on edit page
  5. Click on the Add Web Part link in the area where you want in to display
  6. Your custom web part should appear under the Miscellaneous section with the title and description specified.image
  7. Click on the add button and Voila!image
  8. Add some finishing touches with css, etc. as you prefer!


Addtional Resources:


SharePoint 2010 Update!

For whatever reason, Microsoft decided to make SP 2010 harder for end users to implement a similar solution. You must now encode characters (see below).


  1. Use Site Objects > DataSources > Soap Service Connection to set up the same QueryEx datasource (remember to name it well, I usually prepend custom sources with the client  or the company doing the work)
  2. Create a test page > insert > Data View > Select your Data Source
  3. Modify Layout as desired

Encoded QueryXML String

&lt;QueryPacket xmlns=’urn:Microsoft.Search.Query’&gt;&lt;Query&gt;&lt;SupportedFormats&gt;&lt;Format revision=’1’&gt;urn:Microsoft.Search.Response.Document.Document&lt;/Format&gt; &lt;/SupportedFormats&gt;&lt;Context&gt;&lt;QueryText language=’en-us’ type=’MSSQLFT’&gt;select title, path, Description, contentclass from scope() where (contentclass=’sts_site’ OR contentclass=’sts_web’) &lt;/QueryText&gt;&lt;/Context&gt;&lt;Range&gt;&lt;StartAt&gt;1&lt;/StartAt&gt;&lt;Count&gt;1000&lt;/Count&gt; &lt;/Range&gt;&lt;/Query&gt;&lt;/QueryPacket&gt;

Unencoded QueryXML String

<QueryPacket xmlns=’urn:Microsoft.Search.Query’><Query><SupportedFormats><Format revision=’1′>urn:Microsoft.Search.Response.Document.Document</Format></SupportedFormats><Context><QueryText language=’en-us’ type=’MSSQLFT’>select title, path, Descriptio.n, contentclass from scope() where (contentclass=’sts_site’ OR contentclass=’sts_web’ OR contentclass:’sts_listitem_850′) </QueryText></Context><Range><StartAt>1</StartAt><Count>1000</Count></Range></Query></QueryPacket>

NOTE: contentClass:sts_listitem_850 refers to publishing sites.

Additional Information:

Thanks Mike!

And Laura!

  1. michael clark permalink

    did you ever update this for 2010

  2. Justin permalink

    Do you happen to have a similar solution for SP 2010?

  3. emersonbruce permalink

    escaping characters is needed in 2010, use html character refs for left and right angle brackets
    use the Soap data source to create the connection

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: