Information about users store

Question

Where is information about users associated with specific SharePoint site stored?

I guess information is stored in relational database.

To be more specific i want to ask if it is possible to query this database and get list of all users which have access to site mysubdomain.mydomain.com/sites/xyz where xyz is set of sites and to complicate it more, there is also abc which represents sub-sites which have different set of members and visitors group and there is also another group with different permission level.

As example lets have mysubdomain.mydomain.com/sites/aaa/bb and than mysubdomain.mydomain.com/sites/aaa/cc and this two sub sites have different set of user. So to summarize my question. I want to get as result of SQL query table with username (NT\login) with information about sites/sub-site and permission level.

My question is: Is this possible? Another question: Is there any method how to get this information without installing anything on server side of SharePoint farm? I have list of sites and sub-sites with URL's.

More details:

Let me describe one solution I have now. I’ve found tool Data Juggler (http://www.djuggler.com/) which can automate repetitive web tasks. This means that I’ve prepared list of all sites/sub-sites in my scope. As a source I use spreadsheet with all details about sites/sub-sites input.xlsx this is only one part of process maintained manually. It was big effort to take all data together, but once this was done, it is easier to maintain, as new sites/sub-sites are created occasionally Information in this spreadsheet are in this order:

• URL to Members group (users with Read/Write access)
• URL to Visitors group (users with Read Only access)
• REGION
• Site/Sub-site URL
• Site Name
• Sub-site Name
• Site owner

Ok that’s introduction, now how it works:

• After loading scrip to Djuggler it pops up a window asking you to browse to input spreadsheet (input.xlsx)
• Then it pops up window asking you for number of records in spreadsheet (this step could be possibly omitted if I will tweak the script, but I made this solution in one night learning from examples and building script)
• After that it loads all the information from spreadsheet to Djuggler’s temporary grid and start loading values from columns to variables
• First it loads URL to Members group and rest of columns except URL to Visitors group and uses its internal browser to load page from URL to Members group
• Application start searching in html source of loaded site and looking for specific html tags account=”something” and email=”something” and stores something into variable, than store new row into another grid with all details Account, Email, REGION, Site/Sub-site URL, Site Name, Sub-site Name, Site owner and loops through whole html source, than it jump in loop to another URL to Members group and loops again trough html source and this continue, till loop trough URL to Members group will run for x times, where x is number of rows submitted in second step
• After this loop jump to second column with URL to Visitors group and whole process with loops is repeated.
• At the end application store temporary grid with results from loops into spreadsheet and opens this spreadsheet.

I know that email address and NT\login is pretty the same information so email could be omitted to avoid redundancy of information. And rest of information like region, site name, sub-site name and site owner could be added in second step. For me it is more logical to have just information pulled from site Site/sub-site URL, NT\login of user and permission level (contribute, read, …) So for now I have some solution, but it is “like scraping your head behind left ear with right hand from back of your head…” I am looking for something more elegant.

Answer 1

SharePoint's Web Services can help with this. Take a look at my jQuery Library for SharePoint Web Services and the Web Services documentation on MSDN. (I have links to the MSDN docs for all the Web Services and operations I have "wrapped" on the library site.) If you'll need operations that I haven't wrapped yet, let me know and I'll get 'em in there for you.

You'll need to do some iterating through things, but I think everything you are describing is possible, and there's zero required server-side.

Also, it's possible that you could do this with a Data View Web Part (DVWP) with DataSourceMode="Webs".

Bottom line is that you have some options, and I think it's possible.

Answer 2

The data is stored in SQL Server, but you cannot access it directly from the database in any supported way. You need to use one of the many existing methods i.e. the SharePoint API (Microsoft.SharePoint.dll) or the built-in web services, an example of which is the 'Webs' web service at site/_vti_bin/webs.asmx.

As Marc has stated you could use some client side code to access the web services, but you'll need to ensure that the user accessing the web services has appropriate permissions on all the sub-sites.

Answer 3

Assuming that this question is real and not just spam for that DataJuggler thing, there is a pretty easy way to get the info you want assuming you have a copy of Microsoft Access 2007 handy.

• Open up a new Access DB, and go to the External Data tab
• Click "SharePoint List" on the Import section, put in the URL to your site, and click the "Link to external data" option
• Pick any of the lists (it doesn't really matter which one)
• When Access pulls in your list info, it will also show you a table called "User Information List"

The user information list is one that is normally sort of hidden from the SharePoint UI, but it basically has all the users who have ever been to your site collection or been selected in a people picker somewhere in the site collection. You should be able to export this from Access to anywhere else you care to deal with it, and it will work for as many site collections as you need. Note that it is a read-only view; you will not be able to edit the values in the user information list directly this way.