Global Site Directory for SharePoint 2010

This is an updated post that was created for SharePoint 2007. The general concept remains identical to the older version, but the specific steps have changed quite a bit. Note, there is a section at the bottom that lists potential issues in setting this up.

There are plenty of strategies for developing a global site directory for SharePoint. But a common request is to be able to list all of the sites that the current user has access to. One way to do this would be to write code that iterates though all of the sites in every site collection in every application and checks their permissions. That would function, but of course, performance would be awful. If you have MOSS, the search index already has the list of sites and permissions. All we have to do is write the query. And, since SharePoint designer can work with web services, there’s no code required. The major flaw with this method is that the data is as current as the index. So if someone just gained permission to a site, it will not show up in this list until the next time the indexer runs.

In SPD 2010, open the “Data Sources” section on the left, and click “SOAP Service Connection”
In the “Service Description Location” box, type in the path to the search web service: http://servername/_vti_bin/search.asmx?wsdlNote, the server name should be just that: it should not be a FQDN. (And, make sure you put in the “?wsdl” as well.)
Click “Connect Now”
Make certain you select the QueryEx operation, and not the default Query. Query will return a blob of xml, QueryEx returns a dataset. Of course, a dataset is still xml, but it will be in a format that SPD knows how to use. The bottom of the dialog will then show that there is one parameter, the query Xml. So, click on modify, and …
Paste in:
<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, contentclass from scope() where (contentclass='sts_site' OR contentclass='sts_web') </QueryText></Context><Range><StartAt>1</StartAt><Count>1000</Count> </Range></Query></QueryPacket>

The queryXml deserves its own chapter in a book. Certainly, one of the key pieces is the select statement embedded in the middle. For more info on what’s available, do a search for MSSQLFT, or take a look here:

Note that the “where” statement is the key here, without it the search would be returning all content in SharePoint, including documents, list items, etc. (which is actually pretty fantastic, but not what I’m trying to do here)


The sql above is encoded. Note the &lt; and &gt; that are embedded within the query. These represent < and > respectively. And, unfortunately, this is required. If it is not encoded, the query will just generate an error.

Here is the above statement, unencoded:

<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’) </QueryText></Context><Range><StartAt>1</StartAt><Count>1000</Count></Range></Query></QueryPacket>

Looking at the more readable version, you can see that the query is for all items that are either site collections (sts_site) or sites (sts_web). The results will start with the first result (as opposed to starting with the 20th for paging purposes.) It will show up to 1000 results (which is, of course, excessive).

On with the procedure:

While in the data source dialog, switch to the “General” tab and take note of the data source name, or rename it to something more appropriate.
Click OK to close out of the data source dialog. A data source is now set up, now we just need to use it.
Either open an existing page in SPD or create a new one. One way to create a page is to click on site pages in SPD, and then click on Page à ASPX in the ribbon
Click you cursor in the page where you want the directory to go, and in the ribbon, select “Insertà DataView” and scroll down on the list. You should see your data source towards the bottom of the list.
That’s it! It’s just formatting from here on.

Things that could go wrong:

  1. If the query (xml) is not quite right, you’ll get a message in SharePoint Designer that informatively tells you that it didn’t work. (By informatively, I mean that it provides no additional information. L)
  2. If search is not fully functional on your site, you will get the above mentioned non-informative message. This may seem obvious, but check to make sure that you can do a search on the site before trying to set up this data source in SharePoint Designer. (The error message in SPD will give no hint that there is an issue with search itself, it will just suggest that something didn’t work.)
  3. The URL to the search web service should be a server name, not a FQDN. Having a FQDN there will result in a non-specific error.

19 thoughts on “Global Site Directory for SharePoint 2010

  1. Brandon Lucas

    I am copying the query exactly as you have it pasted above yet SPD keeps throwing an error that the query is bad. I have also tried using the unencoded version with no luck.

      1. Brandon Lucas

        Hi Mike, just confirming here.. search is fully functional. I am not using the FQDN.. and I am copying and pasting exactly what you have shown above. I went on a limb and tried the unencoded version because nothing else was working.

    1. Craig

      Brandon, did you ever get it to work. I am having the same issue. Was it because your web application was claims based?

  2. Manjiri

    I am too getting same error as Brandon “check format and content of the query” in SPD . I am copying exact same query . I confirmed that Search is working and also confirmed the server name , tried with local host too . Any idea ? Thanks

      1. Brandon

        It does use SSL and that article did help. Now I am battling a 401 Unauthorized error when the SOAP connection tries to initiate. Still searching on this one..

      2. Brandon

        Little update: If I set the SOAP service to run as my credentials then it does start working. Obviously, I don’t want to do that since it stores it in clear text. I have tried Windows Authentication, not authenticating as well, with no luck. We are using kerberos to authenticate on this web application.

  3. Manjiri

    Thanks for the update . I do have SSL enabled Webapplication but the search WEbservice http://servername is not SSL protected. To be more specific we have WEbapplication as cname which is ssl enabled but central admin is not . Do you think i still need to follow certificate procedure ? Looking at the ULS viewer I do not see any certificate error but am getting “SOAP exception: System.Net.WebException: The remote server returned an error: (500) Internal Server Error. ”

    1. Mike G Post author

      Another item to check: be sure that AAM is set up correctly for the site your are searching on. But also, there may be an issue with connecting sharepoint designer to http://abc, and then trying to pass windows credentials to http://xyz, even though both sites are on the same server.

  4. Pingback: SharePoint Service Getting Intermittent 401 Unauthorized Error | tory douglas

  5. Craig

    Was your web application claims based or classic mode authentication? I can’t get SOAP services to work in claims based web applications. I am setting the Login tab of the SOAP data source to don’t attempt to authenticate. I end up getting a server returned a non-specifice error message in designer.

  6. Pingback: How to create a Site Directory in SharePoint 2010 « SharePoint Obstacles and Solutions (S.O.S.)

  7. sp-sos

    Mike – I was inspired by your post and realized an eaiser way to accomplish this. I was searching for key word queries when I came across your article. I found in the query the reference to the “contentclass” for sites and subsites, and realized that we could do what you did 100% in SharePoint by using a search scope and a fixed query. It’s easy and doesn’t require any styling. I posted the solution on my blog site:

    1. Mike G Post author

      Excellent! As you point out, using the search parts can also be styled via xsl. Some folks may prefer working in design view in SPD compared to working with the xsl of the search results part. However, since it was just announced that SPD 2013 has dropped support for the design view, my weak argument is even weaker…

  8. Pingback: Show All Sites User Has Access to In SharePoint 2007 | Chaya - Digital Zen

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s