Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Finding All sites that a user has access to?

  Asked By: Colin    Date: Nov 20    Category: Sharepoint    Views: 7868

I know that it is not recommended to touch the database directly,
unfortunately I am there and I'm very frustrated with it. Please help.


I need to get all the WSS sites that a particular user or security
group has been added to. (I already have the code to get a recursive
listing of a user and all the AD groups that they are a member of)
The result from the query below returns all the sites that a user was
EVER added to, so even though the user is deleted there still seems
to be an entry there. Is there some flag somewhere that can tell me
what the status of the user, in relationship to permissions to a
site, currently is.

Select w.Title
From UserInfo u
Inner Join Webs w on u.tp_SiteId = w.SiteId
Where tp_login in ('domain\username', 'domain\group')
And w.FullUrl Like '%/%'

Alternatively is there a better way to go about getting an exhaustive
listing of ANY WSS site that a user is a part of regardly of if the
user has access through Security Group, thier domain account,
inherited permissions from a parent site, broke inheritance and was
explicitly added, and most importantly after being deleted simply
doesn't appear.



21 Answers Found

Answer #1    Answered By: Adrienne Greene     Answered On: Nov 20

I had to go through some pretty complexe steps recently when we were forced to changed servers and domains for our primary SharePoint site. It is internet facing and has a couple of thousand users. In that process we had to do someting similar to what you describe below.

After reading your question I recalled that entries in the UserInfo table are not modied as the result  of a user  being deleted  from a site. It is the SiteGroupMembership table you need to look at, but its not going to be easy. You are going to have to join  the UserInfo table and the the SiteGroupMembership table to do your query, as the later only has three fields: SiteID, GroupID, and MemberID.

Answer #2    Answered By: Joshuah Huber     Answered On: Nov 20

Not ready for release yet..................

Answer #3    Answered By: Gopal Jamakhandi     Answered On: Nov 20

Please do not query  the database  directly. Instead, you need to crawl
the entire Web Farm using the API and look in every nook and cranny.
This is not necessarily easy. I hope to have an exhaustive User
Management tool (Clone User, Delete User, Replace User) out no later
than the end of first quarter 2006. Keep your eyes peeled.

Did I say this was not easy?

Answer #4    Answered By: Keenan Whitehead     Answered On: Nov 20

I have a similar need to find all the webs  a user  has access  to.
However, I am worried about performance. With a DB access, I can get
the full list of all the webs available, and go through each one.
However, I am not sure the API will be able to give me that kind of a
list in any reasonable time. Especially, once we are in full
production, there will be 16k+ MySites plus all the other sites.

Any thoughts or musings?

Answer #5    Answered By: Damon Garner     Answered On: Nov 20

One option -- download the Report Pack for SPS. It includes a program that inspects the portal and stores information in a separate database.

The report pack: www.microsoft.com/.../details.aspx
Source code: www.gotdotnet.com/.../workspace.aspx

Answer #6    Answered By: Dameon Dejesus     Answered On: Nov 20

I forgot to add...

Perhaps you can extend the program to include the user  accessibility you require.

Answer #7    Answered By: Tejaswani Barve     Answered On: Nov 20

Can you go into more detail as to why direct queries to the database  are
not advised? I fully understand that any query  containing an UPDATE or
INSERT operation is a horrible idea but why would SELECT queries be
necessarily a bad idea?

I specifically have an InfoPath form that queries the profile database
for users and departments. It seemed like an easy and harmless way to
get user  information. If it is a lock issue would setting the isolation
level to Read uncommitted be an acceptable work around or are there
still other issues to account  for?

Answer #8    Answered By: Harshita Padwal     Answered On: Nov 20

Just my 2 cents here.

The problem with doing a direct access  for anything on the database  is that you have no guarantee that it's going to remain the same architecture/design for the database going forward with either updates or new versions. Using the API is a much better way in terms of reliability, and re-usability for working with Sharepoint. Even though it's not an easy thing to do, we are sticking with using the API vs directly  working with the database.

Not to mention it makes the DBA's much happier not having us programmers traipsing through the data.

Answer #9    Answered By: Jennifer Jones     Answered On: Nov 20

The other concern is that there is a lot going on with regards to locking,
database optimization, joins, etc. at the db layer by SharePoint. Even doing a
simple query  might put your portal into a deadlock scenario  (trust me, I've done
it just to show people that it can happen).

Stick to the documented and supported API and you can't go wrong. Yes, it's
"easy" to whip off a SQL statement to grab data but it's just a bad place to be.
Either use the API or use the SQL-like syntax of Search to get what you need.

Answer #10    Answered By: Annie Norris     Answered On: Nov 20

Ok, I’ve abandoned the direct access  approach and I’m going to muscle my way through the API.

Now the problem that I cannot seem to overcome is when I start going through the sites/webs under my WindowsIdentity everything works fine, of course this break when I run the web part  as our test account. I use the class from blog -> RevertToAppPool I place this code  right before I start looping through the sites  and now the result  is an error:

Cannot complete this action. Please try again.

Line: For Each User As SPUser In Site.Users

Before I get to this line of code the AppPool has successfully grabbed the virtual server and done some object model stuff relating to site  collections and webs  but for some reason looks to be breaking on mySites. All of this code in running under the context of the application pool set up to be [machine]\sqlservice. Interestingly enough if I set that line with a break point and use the immediate window to return the impersonation to my own account  it will not through an error, but then the test account or most anyone else for that matter cannot run the web part receiving a windows challenge box that is not satisfied with their credentials.

Answer #11    Answered By: Chadd Hahn     Answered On: Nov 20

Sure, from the hip:

1. database  locking potentially caused by your non SharePoint queries.
2. Competition for other database resources like connections, threads,
3. The likelihood that the SharePoint content database will change over
time (it changed with SP1 and with SP2).
4. Loss of SharePoint business logic built into the API.
5. Loss of SharePoint security  built into the API.
6. Loss of SharePoint data validation (very little RI in the database).
7. Subtleties in the schema that may not be fully understood.

Answer #12    Answered By: Cheryl Kelley     Answered On: Nov 20

As for point #2 would you recommend that the SharePoint database  have
its own exclusive SQL Server or would sharing the server with other
databases be acceptable?

Answer #13    Answered By: Kalyan Pujari     Answered On: Nov 20

That isn't what I meant. For instance, let's just say that your SQL
Server has 10 connections for your SharePoint database. SharePoint will
cache these using one set of credentials so that the connection doesn't
need to be created again and again. However, a custom query  will take at
least one connection out of the pool and when it is returned it will
need to be recreated.

Answer #14    Answered By: Jagdish Joshi     Answered On: Nov 20

How are you getting the instance of your SPSite? Are you checking to see
if it is Ready?

Answer #15    Answered By: Shara Johnson     Answered On: Nov 20

I will make sure I go through the
API from now on and retrofit what I have done directly  through SQL.

Answer #16    Answered By: Christop Mcfadden     Answered On: Nov 20

This is certainly a problem. So, we crawl the SharePoint Farm via the
API on a periodic routine basis and glean out the information that we
know cannot be gotten swiftly in real time.

Answer #17    Answered By: Cassidy Sharpe     Answered On: Nov 20

On that note, are there any events that would be triggered
when a site  is created? Or is the only way to keep an up to the minute
list to modify all the site definitions which would add the site to a
list once it is created?

Answer #18    Answered By: Damon Garner     Answered On: Nov 20

Here is the starting point of my API calls

Dim globalAdmin As New SPGlobalAdmin

For Each vServer As SPVirtualServer In globalAdmin.VirtualServers

If vServer.State = SPVirtualServerState.Ready And _

vServer.Url.AbsoluteUri = PortalUrl.ToLower Then
LoadSiteCollectionNodes(vServer, VirtServ)

End If



All of that seem to run just fine but inside LoadSiteCollectionNodes() If appears that if I do anything with a site  property I get the Error. Below, I can access  properties on the SiteColletion object such as the .Url & .RootWeb prop. But it’s when I try to get to the SPWeb props such as TopLevelSite.Title that it breaks. The commented out IsMember function breaks because it is accessing the .Users property and if I were to uncomment the 'Reverter.ReturnToImpersonatingCurrentUser() and run the code  under my WindowsIdentity everything works fine =(

Private Sub LoadSiteCollectionNodes(ByVal vServer As SPVirtualServer, ByVal vServerNode As TreeNode)

For Each SiteCollection As SPSite In vServer.Sites

Dim SiteCollectionNode As New TreeNode

If SiteCollection.Url + "/" <> PortalUrl.ToLower Then

Dim TopLevelSite As SPWeb = SiteCollection.RootWeb

'If IsMember(TopLevelSite) Then
_DebugUniqueSitePerms &= "<B>" & TopLevelSite.Title & " </B>Has Unique Perms:: " & TopLevelSite.HasUniquePerm.ToString() & " "
Dim TopLevelSiteNode As New TreeNode
TopLevelSiteNode.Text = TopLevelSite.Title
TopLevelSiteNode.NavigateUrl = TopLevelSite.Url
LoadChildSiteNodes(TopLevelSite, TopLevelSiteNode)
'End If

End If


End Sub

Answer #19    Answered By: Hans Weiss     Answered On: Nov 20

I suspect that you are crossing into one of the three other application
domains that SharePoint creates. You may want to look at my original
Using Credential-less Impersonation SharePoint Advisor article:
http://mssharepoint.advisorguide.com/doc/16238. Failing that, you will
need to create your own App domain  or Web Service so that the current
http context has no influence on the security  context in which your code
runs. Check out Maurice Prather's blog, SharePoint Thoughts:

Answer #20    Answered By: Alison West     Answered On: Nov 20

A few things …

1) “Cannot complete this action. Try again.” Is the catch all for when the unmanaged code  ran into an error – and didn’t return the specific error message or condition.

2) The underlying unmanaged code is pretty weird about how it deals with identity. Maurice Prather (BlueDogLimited.com) talks about using AppDomains to get around some of those problems. Check out his post … www.bluedoglimited.com/.../ViewPost.aspx?ID=198

Answer #21    Answered By: Freddy Heath     Answered On: Nov 20

I'd use ExecuteUrl for each Configuration that you want to keep track

Didn't find what you were looking for? Find more on Finding All sites that a user has access to? Or get search suggestion and latest updates.