none
Permissions for random users lost out of nowhere

    Question

  • This has happened to me a handful of times since implementing SharePoint 2010.  Yesterday a user had full access to his site as a Site Owner.  Today, his permissions were gone.  Even my own permissions were gone.  I mean every permission for our usernames that was assigned to groups and to unique areas were removed,  everywhere. 

    The Audit log does not show any permissions changes regarding this.  I had done Windows Updates on the SharePoint and SQL servers last night and rebooted them, and a full nightly backup happened last night.  My intial thought before when this happened was that the SP/SQL servers were rebooted when someone was in SharePoint files or sites, cause them to somehow get their permissions wiped clean, but I've had no trail to follow.

    I suspected something with User Profile Service Application and the profiles not synching, but that sync process is working fine.  I have to manually reset the permissions for my account (which is in the Owners group of all the sites, the Site Collection Administrators group, etc.), and for this other user, with no understanding of what caused it and no known way of restoring all the permissions at once (tried a full synchronization of the user profiles), even the unique permissions that would have been set on specific libraries/files/folders.

    This has happened with random users, about 5 times now.  I'm on the latest CU (Feb 2012).  I'm not sure where to look for the cause...has anyone else experienced this?


    • Modifié TheMind jeudi 12 avril 2012 14:49
    jeudi 12 avril 2012 14:45

Toutes les réponses

  • can you verify people are not yanking permissions from sites above the site in question and the subsite is inheriting?
    vendredi 13 avril 2012 00:14
  • can you verify people are not yanking permissions from sites above the site in question and the subsite is inheriting?

    Yes, there is no one above me that could have removed my permissions from the Site Collection Admins group, or my own department site as examples.  On the IT department site, I'm the only one with permissions. 

    It's an across the board, all sites issue when it happens.  Top level sites on down through unique areas.  Even when subsites are not inheriting and have unique permissions, the permissions for that user are removed there as well.  It's almost as if there is a corruption issue with a user account at any point in time and it just wipes the permissions away...yet the User Profile still exists and thus the person can have permissions re-added.


    • Modifié TheMind vendredi 13 avril 2012 02:09
    vendredi 13 avril 2012 02:08
  • Hi, did you find a solution for this? I am having the same problem!

    ceren

    mardi 17 avril 2012 09:30
  • Hi, did you find a solution for this? I am having the same problem!

    ceren


    I have not yet figured it out, and have not yet found anyone else who could help with this.  Hopefully someone on here comes across an answer and can share that information.
    jeudi 19 avril 2012 14:26
  • Hello,

    I may have experienced a similar issue but was never able to recreate it which led me to believe a third-party solution or other customization was to blame.  

    In my case a site collection was deleted from the second stage recycle bin causing default Sharepoint security groups in all sites to be deleted so every member was unable to log in unless they had policy for web apps.  However, the user account running the timer job service was actually logged as deleting the groups when I checked the Sharepoint audit logs.  The version of Sharepoint was Enterprise edition with Service Pack 1 and June 2011 CU.  The only way to restore the sharepoint groups was to restore the content database which restored all permissions across the board.  Is the same issue you have experienced?  Are the actual Sharepoint security groups being removed?  Is there any pattern to the user permissions being removed (i.e. certain time of day or certain groups being affected)?  Also, when you say auditing are you referring to the Sharepoint site collection auditing or just windows security logs?


    -Chopps

    jeudi 19 avril 2012 14:51
  • Hello,

    I may have experienced a similar issue but was never able to recreate it which led me to believe a third-party solution or other customization was to blame.  

    In my case a site collection was deleted from the second stage recycle bin causing default Sharepoint security groups in all sites to be deleted so every member was unable to log in unless they had policy for web apps.  However, the user account running the timer job service was actually logged as deleting the groups when I checked the Sharepoint audit logs.  The version of Sharepoint was Enterprise edition with Service Pack 1 and June 2011 CU.  The only way to restore the sharepoint groups was to restore the content database which restored all permissions across the board.  Is the same issue you have experienced?  Are the actual Sharepoint security groups being removed?  Is there any pattern to the user permissions being removed (i.e. certain time of day or certain groups being affected)?  Also, when you say auditing are you referring to the Sharepoint site collection auditing or just windows security logs?


    -Chopps

    No, we did not have any site collections deleted, nor were actual SharePoint security groups being removed.  There hasn't been a known pattern to the lost permissions, but it has happened to a couple people more than once (myself and another user both on two different occasions), and a couple other people just one time.  I've only noticed it on a new day when a user complains of lost access that they had the day before (which could indicate an overnight issue happening), and only noticed it happened to my own account when trying to reset someone else's permissions, only to find that mine were gone as well.

    For auditing, I'm referring to the actual SharePoint audit logs found in Site Collection Administration -> Audit Log Reports -> Security Settings, as well as looking at a custom report filtering for user permission changes.  I can see other permission changes in these audit logs that have happened, but when users lose permissions, there is nothing noted in these logs. 

    This is a case of a user losing access to everything they were given access to, including removal of their name from SharePoint security groups.  It would make sense if it's an end user, or even a Site Owner, that someone else could have removed their permissions...but no one could have removed my permissions, and it has happened to my account.  I've manually re-added the permissions each time this has happened, and it gets very cumbersome and tedious when there's been many unique permissions set across different areas. 

    I have not gone the lengths of restoring content databases, because everything else seems to be fine otherwise.


    • Modifié TheMind jeudi 19 avril 2012 15:04
    jeudi 19 avril 2012 15:02
  • I guess it was not the same issue.  Although, I should correct myself in that the user account logged as deleting the groups was the user account who deleted the site collection which led to some confusion as that user did not have the appropriate rights.  

    This may be a long shot but are you by chance running the Cell Storage User Data Deletion Job timer job at all?  That's the only thing I can think of that could potentially cause loss of data and since it's a timer job it would trump your permissions as well.


    -Chopps

    jeudi 19 avril 2012 15:19
  • I guess it was not the same issue.  Although, I should correct myself in that the user account logged as deleting the groups was the user account who deleted the site collection which led to some confusion as that user did not have the appropriate rights.  

    This may be a long shot but are you by chance running the Cell Storage User Data Deletion Job timer job at all?  That's the only thing I can think of that could potentially cause loss of data and since it's a timer job it would trump your permissions as well.


    -Chopps


    No, the Cell Storage User Data Deletion job is disabled on all web applications.  I'm still stumped at the moment myself.
    jeudi 19 avril 2012 15:21
  • Well, I've been trying a few things in my test farm but so far haven't been able to re-create a similar issue.  The only thing I could think of that may cause this kind of issue may be a scheduled task or something one of the servers in the farm.  Have you checked for any scheduled tasks yet?

    You may also want to run the following T-SQL statement against the content database in SQL Server Management Studio the next time it happens:

    SELECT DISTINCT tp_Login, tp_IsActive, tp_Deleted FROM dbo.UserInfo ORDER BY tp_Login ASC

    The user accounts are never deleted out of the content databases even if they are deleted from sites.  Instead, the tp_Deleted flag will have a number of days value since the user was deleted from the site or site collection.  It should give you a good idea of the time frame and a review of the ULS logs during that time may provide additional information.  Please post any additional information you find.


    -Chopps

    jeudi 19 avril 2012 16:01
  • Well, I've been trying a few things in my test farm but so far haven't been able to re-create a similar issue.  The only thing I could think of that may cause this kind of issue may be a scheduled task or something one of the servers in the farm.  Have you checked for any scheduled tasks yet?

    You may also want to run the following T-SQL statement against the content database in SQL Server Management Studio the next time it happens:

    SELECT DISTINCT tp_Login, tp_IsActive, tp_Deleted FROM dbo.UserInfo ORDER BY tp_Login ASC

    The user accounts are never deleted out of the content databases even if they are deleted from sites.  Instead, the tp_Deleted flag will have a number of days value since the user was deleted from the site or site collection.  It should give you a good idea of the time frame and a review of the ULS logs during that time may provide additional information.  Please post any additional information you find.


    -Chopps

    I ran the T-SQL statement now for the heck of it.  There is a tp_Deleted value for each of the accounts that have had permissions randomly lost, while the rest say 0...however, the values range from 191-207 for the accounts in question, and if these are days since deleted, this is not an accurate reflection of the lost permissions that happened a week ago.

    I don't believe I can go back 191 days in ULS logs to review this.  Also, I had looked at ULS logs the day I opened this thread (the day after it happened most recently), and didn't at the time see anything unusual, but I could have missed something.

    jeudi 19 avril 2012 17:09
  • Hi, Can I find the same thing about groups? I mean is there a query to find deleted groups and how many days ago they were deleted?

    Thanks


    ceren

    vendredi 20 avril 2012 15:17
  • Ceren,

    Unfortunately, the same does not hold true for groups (at least not to my knowledge).  From what I've seen they are completely removed from the content database altogether.  You would be better off just enabling site collection auditing for that one.

    TheMind,

    Unfortunately, I am at a loss as to what may be going on in your case but I do find it rather strange that has a tp_deleted value that high.  To my knowledge, when you add a user back after they have been deleted it creates a whole new row for that user instead of changing the tp_deleted value so you may want to verify there aren't multiple entries for that user name with a more recent value. 


    -Chopps

    vendredi 20 avril 2012 15:33
  • Thanks Jimmy but its now my value ^-^ I think it's 'the Mind's.

    I have already enabled the auditing but the audit logs are terribly hard to understand and there is no documentation at all..


    ceren

    vendredi 20 avril 2012 15:42
  • Ceren,

    Unfortunately, the same does not hold true for groups (at least not to my knowledge).  From what I've seen they are completely removed from the content database altogether.  You would be better off just enabling site collection auditing for that one.

    TheMind,

    Unfortunately, I am at a loss as to what may be going on in your case but I do find it rather strange that has a tp_deleted value that high.  To my knowledge, when you add a user back after they have been deleted it creates a whole new row for that user instead of changing the tp_deleted value so you may want to verify there aren't multiple entries for that user name with a more recent value. 


    -Chopps

    Chopps,

    For those users (including mine), there are multiple entries, indicating that the old with a deleted value is not active, while the new with no deleted is active.

    Mine says:

    <my username>     tp_isActive: 0    tp_Deleted: 198
    <my username>     tp_isActive: 1    tp_Deleted: 0

    Even here, it doesn't show that there was any deletion of the user a week ago. 

    Interestingly, I looked up tp_Deleted and found this:

    tp_Deleted: A value specifying whether the principal is marked as deleted. If tp_Deleted is 0, the principal is not deleted. If tp_Deleted is not 0, the principal is marked as deleted, and the value is the User Identifier that was associated with this principal. The deleted state can be used for user information, rather than dropping entries from the table, to preserve list item ownership information. A user or domain group with the tp_Deleted value set to nonzero can be restored by setting the tp_Deleted value to 0 and updating other fields as necessary.

    To me, that says that the value set for tp_Deleted (198 in my case) is not days deleted, but rather a User Identifier, and that I could restore the user permissions by setting the deleted value to 0...but I would then have to change the other line for my account that says isActive: 1, I would think?

    vendredi 20 avril 2012 15:52
  • Excellent find on that one.  I had always assumed the value was days as it was an integer value but the fact that it matched in previous cases may have been purely coincidental now that I think about it.

    I would agree that the user with a value in the tp_deleted field would also need to have the tp_isActive value set to 1.  However, you may also want to change the value in the other row for tp_isActive from 1 to 0 as I could see this potentially causing a conflict.  I would definitely test it out on your own username first so other users are unaffected.  Also, could you please include the link where you found that information in your next post?  My googling skills are not what they used to be. ;) 


    -Chopps

    vendredi 20 avril 2012 16:07
  • Excellent find on that one.  I had always assumed the value was days as it was an integer value but the fact that it matched in previous cases may have been purely coincidental now that I think about it.

    I would agree that the user with a value in the tp_deleted field would also need to have the tp_isActive value set to 1.  However, you may also want to change the value in the other row for tp_isActive from 1 to 0 as I could see this potentially causing a conflict.  I would definitely test it out on your own username first so other users are unaffected.  Also, could you please include the link where you found that information in your next post?  My googling skills are not what they used to be. ;) 


    -Chopps

    I pulled that info from here: http://msdn.microsoft.com/en-us/library/dd303914(v=prot.13).aspx

    I've heard the concerns of editing SharePoint tables directly in SQL, and am a little hesitant to do so, as I believe there is more to it than just the one value change...for instance:

    From a bigger, slightly different issue at http://www.k2underground.com/blogs/infrastructure_spotlight/archive/2010/03/11/troubleshooting-and-fixing-a-user-not-found-error-when-creating-a-new-sharepoint-service-instance.aspx

    In the query above there will probably be a user that has its tp_Deleted status set to something other than 0. This is the user causing the User not found error. You need to change this user’s tp_Deleted status to 0 by running the following query, replacing the tp_ID and tp_siteID values in the query with the values for the deleted user from step 4 (Note that tp_siteID is not the same as tp_webID from step 3)

    UPDATE UserInfo

    SET tp_Deleted = 0

    WHERE tp_Id = 16 AND tp_SiteID = 'E9F91A16-535F-457C-A075-D034171E0F16'

    I have also found this thead, which looks much like what I'm going through, although it is someone on MOSS 2007: http://social.technet.microsoft.com/Forums/lv-LV/sharepointadmin/thread/feea1f2e-7bf4-4570-837c-6cbb8bd9bfe5

    vendredi 20 avril 2012 16:25
  • Thanks for that link.

    You are right to be hesitant about making those changes to the database.  It does appear to be a bit more involved than originally anticipated.  Unfortunately, someone has decided to delete my cloud servers which means my Sharepoint labs are gone.  Otherwise, I would have happily done some more testing there and let you know the results.

    I think we may need to take a step back and focus on what could have possibly caused the issue rather than the side effects as I fear we may be going down a very long rabbit hole on that one.

    Those other threads may be a step in the right direction as a timer job is the most likely culprit.  Let me do some more research on this and see what I can find.  I'm thinking if we can narrow it down to timer jobs that are suspect you can do a search through the ULS logs using the SP-GetLogEvent cmdlet looking at every instance they ran during a specific time frame but I'm not quite sure if it will have logged what the timer job did.  

    In the meantime, have you checked the Health Analyzer to see if there are any weird issues being reported?


    -Chopps

    vendredi 20 avril 2012 17:03
  • Thanks for that link.

    You are right to be hesitant about making those changes to the database.  It does appear to be a bit more involved than originally anticipated.  Unfortunately, someone has decided to delete my cloud servers which means my Sharepoint labs are gone.  Otherwise, I would have happily done some more testing there and let you know the results.

    I think we may need to take a step back and focus on what could have possibly caused the issue rather than the side effects as I fear we may be going down a very long rabbit hole on that one.

    Those other threads may be a step in the right direction as a timer job is the most likely culprit.  Let me do some more research on this and see what I can find.  I'm thinking if we can narrow it down to timer jobs that are suspect you can do a search through the ULS logs using the SP-GetLogEvent cmdlet looking at every instance they ran during a specific time frame but I'm not quite sure if it will have logged what the timer job did.  

    In the meantime, have you checked the Health Analyzer to see if there are any weird issues being reported?


    -Chopps


    Health Analyzer is where I look first generally, and it reports the usual things there, even now.
    vendredi 20 avril 2012 19:42
  • Damn.  It did it again.  Earlier I ran Windows Updates on SharePoint and SQL server, and rebooted them.  I jump in about 5 hours later and my permissions are gone.  So are the unique permissions for the other two people that this has happened to before.

    vendredi 11 mai 2012 03:19
  • Hi, You already know we have the same problem. Escalated to Microsoft and they are still working on it! Once I have the solution or any other information from them I'll happily update.

    ceren


    • Modifié ova c vendredi 11 mai 2012 07:44
    vendredi 11 mai 2012 07:43
  • Hi, You already know we have the same problem. Escalated to Microsoft and they are still working on it! Once I have the solution or any other information from them I'll happily update.

    ceren


    Ceren, thanks for the update!  Everyone in the Content DB that had a value in the tp_Deleted column seemed to have been affected (unique permissions removed, and user was removed from SharePoint groups and permissions overall). 

    I have yet to find anyone, anywhere that knows the cause of this, or a fix for it...so hopefully their answer for you is the answer for all.  At least it seems that I narrowed down my issue to this happening after doing Windows Updates and rebooting the SharePoint and SQL server each Patch Tuesday (or so it seems that way, I don't remember if the previous four or five times that this happened was after Windows Updates or not).

    vendredi 11 mai 2012 19:12
  • I had this issue as well.  It seems that the site collection is getting put into read-only mode.  I'm not sure as to the cause yet, but the following link will resolve the issue.  (at least it did for me)

    http://sprider.org/2011/12/12/sharepoint-site-collection-administrator-lost-full-permission-issue/

    Scott Newman

    mercredi 30 mai 2012 14:30
  • Hey TheMind,

    So, I think I may know what is going on in your farm with the disappearing users but I had a few questions just to verify:

    1. Do the affected users have multiple entries in the content database (UserInfo table)?

    2. When the issue happens to the affected users do all of them have a value in the tp_deleted field?

    3. Does the user's AD SID match the value of the tp_SystemID value (this value would be for the user with a value of 0 for tp_deleted) for that user (when the issue is occurring)?

    I think what may be happening is that those users were either deleted from Active Directory and then re-created or migrated from another Active Directory environment. 

    I actually ran into an issue not too long ago where adding one user to a specific group caused the no-longer valid user in the content database (the one with an invalid SID) to get activated and the correct user to be marked as deleted.  This resulted in that user belonging only to that group and disappearing from the previous groups they were once a part of.  It appears the SID's and group memberships are somehow related (although I'm not quite sure how exactly).  I haven't been able to recreate the issue on my farm yet but I felt this information might be helpful to you.

    You can find the SID for the AD user by using adsiedit and getting the value of objectSID.  It should be exactly the same as the tp_SystemID entry for that user in the content database with the exception of the 0x appended to the front of it.


    -Chopps


    lundi 11 juin 2012 18:35
  • So has anyone found an answer to this issue. We are having the same problem. Users randomly losing rights. Ceren, was Microsoft able to help?
    jeudi 14 juin 2012 19:58
  • Just a quick update. I have found some powershell code which allows you to search the SharePoint change log using an SPQuery object. I've modified the code to better fit the problem we are having. I can track other group membership changes (adds and deletes), but THESE changes do not show up in the change log.

    My working theory is that whatever made the change did not go through the user interface or the object model, but called the stored procedures directly. I'll update as I find more information.

    vendredi 15 juin 2012 13:57
  • I had this issue as well.  It seems that the site collection is getting put into read-only mode.  I'm not sure as to the cause yet, but the following link will resolve the issue.  (at least it did for me)

    http://sprider.org/2011/12/12/sharepoint-site-collection-administrator-lost-full-permission-issue/

    Scott Newman


    I posted this a while ago, but apparently it didn't take.  My site collection was not going into read-only mode or being locked.
    vendredi 15 juin 2012 14:00
  • Hey TheMind,

    So, I think I may know what is going on in your farm with the disappearing users but I had a few questions just to verify:

    1. Do the affected users have multiple entries in the content database (UserInfo table)?

    2. When the issue happens to the affected users do all of them have a value in the tp_deleted field?

    3. Does the user's AD SID match the value of the tp_SystemID value (this value would be for the user with a value of 0 for tp_deleted) for that user (when the issue is occurring)?

    I think what may be happening is that those users were either deleted from Active Directory and then re-created or migrated from another Active Directory environment. 

    I actually ran into an issue not too long ago where adding one user to a specific group caused the no-longer valid user in the content database (the one with an invalid SID) to get activated and the correct user to be marked as deleted.  This resulted in that user belonging only to that group and disappearing from the previous groups they were once a part of.  It appears the SID's and group memberships are somehow related (although I'm not quite sure how exactly).  I haven't been able to recreate the issue on my farm yet but I felt this information might be helpful to you.

    You can find the SID for the AD user by using adsiedit and getting the value of objectSID.  It should be exactly the same as the tp_SystemID entry for that user in the content database with the exception of the 0x appended to the front of it.


    -Chopps


    1) Yes

    2) Yes

    3) I may be looking at the wrong thing, but no, there is no match of ObjectSID to tp_SystemID.  The ObjectSID begins with S-1-5-21...while the tp_SystemID's ALL begin with 0x693A3

    vendredi 15 juin 2012 14:11
  • I'm slightly concerned it will happen again with the Windows Updates that need to be applied to the server, since it seemed to happen each time before when I did Windows Updates, so I'm trying to hold out on updates until there's an answer...
    vendredi 15 juin 2012 14:15
  • Further update. Looking at the stored procedures it appears that they are using transactions and any changes would be logged in the EventCache, so whatever made the changes did not do it calling the stored procedures.

    The following SQL query will get all group membership deletions made during the specified time period for the users with ID 2125 or 4355. Using this code I see users being deleted from groups, but not the group deletions that are problematic. Please note that this is available whether you have configured auditing or not.

    As always, PLEASE BE EXTREMELY CAREFUL WHEN WORKING WITH THE SQL TABLES. 

    use WSS_Content_XXXXX

    select EC.EventTime, EC.EventType, EC.TimeLastModified, EC.ItemName, EC.ItemFullUrl, EC.ItemId
    , EC.ModifiedBy, EC.ObjectType, EC.EventData
    from EventCache EC
    where EC.TimeLastModified between '2012-06-09 10:00:00' AND '2012-06-14 23:00:00'
    and EC.EventType = 4194304
    and EC.ItemId in (2125, 4355)

    vendredi 15 juin 2012 15:34
  • TheMind, if you use this CSVDE command you can get your objectSID in a format that matches the tp_SystemID format:

    csvde -d "cn=XXX,ou=XXX,dc=XXX,dc=XXX" -r  "(objectClass=user)" -l objectSID, sidHistory -f objectSID.txt

    vendredi 15 juin 2012 15:48
  • Just got off the phone with Microsoft support and their recommendation is to apply the February 2012 Cumulative Update and see if it happens again. Has anyone else already applied this update and continued to have problems?

    vendredi 15 juin 2012 18:44
  • Just got off the phone with Microsoft support and their recommendation is to apply the February 2012 Cumulative Update and see if it happens again. Has anyone else already applied this update and continued to have problems?


    My original post at the top indicated that I was on the Feb 2012 CU when it happened.  I've since then put on the April 2012 CU (before the recall), but haven't encountered another lost permission issue since this thread was started.  I'm sure it will happen again though, because I've done nothing to fix it  :)
    vendredi 15 juin 2012 18:50
  • Hi,

    You must have the same patch at dev or UAT. Are you getting the same issue at UAT.

    Check the IIS log, you will have an idea about the person who is in this activity.

    Stop the internet connection at production servers :-)

    vendredi 15 juin 2012 21:29
  • Well, I've got a pretty good idea what is happening now. The reason that searching the change log for a user being removed from a group wasn't working was that they weren't removed from a group as such. The users were being removed from the site by a stored procedure. That puts a different record in the change log than a 4194304. It is a 16384 eventtype in the EventCache table. The user's ID is in the Int0 column, not in the ItemID column.

    Once I could identify the events leading up to a user losing their permissions, I was able to see the pattern. There were essentially two types of users in the UserInfo table; those who had only one entry in the table with their login (domain\user) and those who had two. The users had two entries because of some Active Directory changes we recently went through. In every case, one of those users was active (tp_deleted = 0)  and the other was deleted (tp_deleted NOT = 0). 

    We ran a commercial tool last weekend that copied a site from one location in the site collection to another. That tool uses standard procedures and calls the object model for all changes, so the tool probably wasn't to blame, but during the process user accounts had to be updated at the site collection level. This is where the issue occurred. All users were removed from the site and put back in. The process of removing and putting them back in worked perfectly for the users with only one entry in the UserInfo table, but accounts with two entries had problems. Apparently the STSADM -o import -includeusersecurity command (or it's powershell equivalent) is being used and it works from the user's login (domain\user) and not by the user's ID. It apparently assumes that users will have only one entry in the UserInfo table with a particular login. The result was a confused mess.

    So, to sum up. In our case, the process of export and import of users caused our problem. If you use the queries below you should be able to identify if users were removed from the site and, by looking at the timestamps, determine what process did it.

    This query checks the changelog for users being removed from the site:

    use WSS_Content_XXXXX
    select EC.EventTime, EC.EventType, EC.TimeLastModified, EC.ItemName, EC.ItemFullUrl, EC.ItemId, EC.ModifiedBy, EC.ObjectType, EC.EventData
    from EventCache EC
    where EC.TimeLastModified between '2012-06-09 10:00:00' AND '2012-06-14 23:00:00'
    and EC.EventType = 16384

    The times returned are in UTC so you need to adjust for your timezone. I'm in CDT so I subtracted 5 hours. 

    This query will let you know if users have more than one entry in the UserInfo table:

    select tp_login , tp_title, tp_deleted from UserInfo UI where tp_Login IN
    (select tp_login from UserInfo UI group by tp_Title, tp_login having COUNT(tp_title) > 1)
    order by tp_Title


    EDIT: Also look for 16388 EventTypes.



    • Modifié JLChance lundi 18 juin 2012 22:24
    lundi 18 juin 2012 01:30
  • Well, I've got a pretty good idea what is happening now. The reason that searching the change log for a user being removed from a group wasn't working was that they weren't removed from a group as such. The users were being removed from the site by a stored procedure. That puts a different record in the change log than a 4194304. It is a 16384 eventtype in the EventCache table. The user's ID is in the Int0 column, not in the ItemID column.

    Once I could identify the events leading up to a user losing their permissions, I was able to see the pattern. There were essentially two types of users in the UserInfo table; those who had only one entry in the table with their login (domain\user) and those who had two. The users had two entries because of some Active Directory changes we recently went through. In every case, one of those users was active (tp_deleted = 0)  and the other was deleted (tp_deleted NOT = 0). 

    We ran a commercial tool last weekend that copied a site from one location in the site collection to another. That tool uses standard procedures and calls the object model for all changes, so the tool probably wasn't to blame, but during the process user accounts had to be updated at the site collection level. This is where the issue occurred. All users were removed from the site and put back in. The process of removing and putting them back in worked perfectly for the users with only one entry in the UserInfo table, but accounts with two entries had problems. Apparently the STSADM -o import -includeusersecurity command (or it's powershell equivalent) is being used and it works from the user's login (domain\user) and not by the user's ID. It apparently assumes that users will have only one entry in the UserInfo table with a particular login. The result was a confused mess.

    So, to sum up. In our case, the process of export and import of users caused our problem. If you use the queries below you should be able to identify if users were removed from the site and, by looking at the timestamps, determine what process did it.

    This query checks the changelog for users being removed from the site:

    use WSS_Content_XXXXX
    select EC.EventTime, EC.EventType, EC.TimeLastModified, EC.ItemName, EC.ItemFullUrl, EC.ItemId, EC.ModifiedBy, EC.ObjectType, EC.EventData
    from EventCache EC
    where EC.TimeLastModified between '2012-06-09 10:00:00' AND '2012-06-14 23:00:00'
    and EC.EventType = 16384

    The times returned are in UTC so you need to adjust for your timezone. I'm in CDT so I subtracted 5 hours. 

    This query will let you know if users have more than one entry in the UserInfo table:

    select tp_login , tp_title, tp_deleted from UserInfo UI where tp_Login IN
    (select tp_login from UserInfo UI group by tp_Title, tp_login having COUNT(tp_title) > 1)
    order by tp_Title


    EDIT: Also look for 16388 EventTypes.



    I tried the query for checking the changelog, and since it returned nothing, I'm assuming that it won't help me in this situation since the last time this happened was the day before my original post (so, April 11th), and I probably don't have that stored in the changelog anymore.

    I have 8 different user accounts that have tp_Deleted values, so I believe they all get affected when this happens. I haven't seen a way to properly clean up the UserInfo table, either.

    samedi 23 juin 2012 15:07
  • Just to be sure we are all understanding each other, entries with (tp_deleted not = 0) in the UserInfo table are not a bad thing. It IS a bad thing to have two entries in the UserInfo with the same tp_login. In that case, one of them should have a non-zero value for tp_deleted.

    I am working with Microsoft to get a resolution to this, but, so far, nothing. 

    lundi 25 juin 2012 17:46
  • Just to be sure we are all understanding each other, entries with (tp_deleted not = 0) in the UserInfo table are not a bad thing. It IS a bad thing to have two entries in the UserInfo with the same tp_login. In that case, one of them should have a non-zero value for tp_deleted.

    I am working with Microsoft to get a resolution to this, but, so far, nothing. 


    Correct, but in my case the correlation is that everyone with a non-zero tp_Deleted value has a second entry in the UserInfo table with the same tp_login for the same tp_SiteID...thus I viewed it as bad, because all of those user accounts (one of which is my own) are affected when the random issue happens.
    lundi 25 juin 2012 17:56
  • Yes, that's exactly the footprint we are dealing with, except we only have one Site Collection per database, so I didn't have to look at SiteID.

    It was caused in our case by running [stsadm -o migrateuser -oldlogin domain\user -newlogin domain\user -ignoresidhistory] when the user had already logged into the new farm before we ran the command. Because the user had already logged in, they already had an entry in the UserInfo table, so the migration adjusted the oldlogin one to the newlogin and marked the old one from the new domain as deleted:

    Before running command:

    tp_id__tp_login________tp_deleted

    11___domain1\user1___0

    13___domain2\user1___0

    After running the command:

    11___domain1\user1___11

    13___domain1\user1___0

    The outcome? Two entries in the UserInfo table with the same tp_login, which then leads to users losing rights randomly when you make unrelated changes to permissions on the site collection.

    EDIT: I can't get the formatting right, but that data was in tabular format when I typed it in.

    • Modifié JLChance mardi 26 juin 2012 00:03
    lundi 25 juin 2012 22:40
  • Yes, that's exactly the footprint we are dealing with, except we only have one Site Collection per database, so I didn't have to look at SiteID.

    It was caused in our case by running [stsadm -o migrateuser -oldlogin domain\user -newlogin domain\user -ignoresidhistory] when the user had already logged into the new farm before we ran the command. Because the user had already logged in, they already had an entry in the UserInfo table, so the migration adjusted the oldlogin one to the newlogin and marked the old one from the new domain as deleted:

    Before running command:

    tp_id__tp_login________tp_deleted

    11___domain1\user1___0

    13___domain2\user1___0

    After running the command:

    11___domain1\user1___11

    13___domain1\user1___0

    The outcome? Two entries in the UserInfo table with the same tp_login, which then leads to users losing rights randomly when you make unrelated changes to permissions on the site collection.

    EDIT: I can't get the formatting right, but that data was in tabular format when I typed it in.

    When I look at the UserInfo table, I'll see multiple entries for just about all the users: one for the SiteID that relates to their MySite (which is a Site Collection in it's own database), and another entry for the SiteID that relates to the main intranet. The 8 accounts in question then actually have 3 entries in this table: all three have the same tp_login, one is for the My Site SiteID, the other two are for the intranet SiteID and one of those two have a non-zero tp_Deleted value.

    It would be nice to know what the original point in time was that accounts existed in the UserInfo table and then were again written in, thus creating the non-zero tp_Deleted value entry, and why there isn't a better process for handling the UserInfo database.

    mardi 26 juin 2012 02:00
  • TheMind, have you done any migrations in your environment? SharePoint 2007 to 2010, or from one Active Directory domain to another?
    mardi 26 juin 2012 20:08
  • TheMind, have you done any migrations in your environment? SharePoint 2007 to 2010, or from one Active Directory domain to another?

    No, we started with SharePoint 2010, and have been in the one domain only.
    mardi 26 juin 2012 20:39
  • TheMind, here's something that might help. The issue is that a particular account (domain\username) has two entries in the UserInfo table with the same tp_login (domain\username) and same tp_SiteID. You can't delete the duplicate entries and you can't change the Site ID, but you can change the account login to, perhaps, Domain\UserName1 in Active Directory. Can you change one of the usernames that you know has these duplicates and then run a full Profile Sync and see what happens? The two entries in the UserInfo table have different values for the tp_SystemID (the user's SID) so I hope the tp_login of the "undeleted" entry in the UserInfo table will be changed and the tp_login of the "deleted" one will not. 

    I will try this in my environment, but it will take weeks to get the approval and actually make the change.

    mercredi 27 juin 2012 11:27
  • TheMind, here's something that might help. The issue is that a particular account (domain\username) has two entries in the UserInfo table with the same tp_login (domain\username) and same tp_SiteID. You can't delete the duplicate entries and you can't change the Site ID, but you can change the account login to, perhaps, Domain\UserName1 in Active Directory. Can you change one of the usernames that you know has these duplicates and then run a full Profile Sync and see what happens? The two entries in the UserInfo table have different values for the tp_SystemID (the user's SID) so I hope the tp_login of the "undeleted" entry in the UserInfo table will be changed and the tp_login of the "deleted" one will not. 

    I will try this in my environment, but it will take weeks to get the approval and actually make the change.

    I have not tried this yet. I wanted to update that I did Windows Updates on 6/30 and didn't have an issue (although I staggered the SharePoint and SQL servers when updating and rebooting them). I then did Windows Updates on 7/14 and rebooted the SharePoint and SQL servers at the same time, and a couple of the user accounts lost permissions again, one of which was my own.
    mardi 17 juillet 2012 14:22
  • It's been a while, but I'm updating that we suffered a cooling malfunction that caused our servers to shutdown.  Upon bringing everything back up, I once again had some users lose unique permissions to areas in sites.  There's probably more than the few that already notice permissions gone, but I won't know until they encounter their missing files.

    I'd love for this to never, ever happen again.  Seriously.

    vendredi 26 octobre 2012 16:05
  • Add one more to the list of sites that have been experiencing this problem (since we upgraded from SPS 3.0 to a SP2010 server).  Right now I'm compiling a spreadsheet that contains every site's permissions - kind of a PITA but it's getting difficult to remember what the permissions were each time they get removed.  We are a small company that has a site with a dozen tabs (subsites) and each tab has a different set of permissions based on who in what department gets to read / write what information.  What I am doing is mapping each set of permissions to an active directory security group.  This way when the permissions get wiped out, I can just reassign that security group.  (The saving grace is that people are not randomly disappearing from AD security groups.)  A program that simply did not erase your configuration would be better - but after dealing with some sharepoint 'restores', I think it's safer to document every customization you make to a site, or you have no way of fixing things, or knowing 'how they should be configured'.

    mercredi 20 mars 2013 12:53
  • Hi sorry for not posting for a while. Microsoft came back to us and said that this issue is a bug.

    what happens is you have a site and you copy this site using export/import with the include security option. Then you delete the old site and the imported site remains there. 30 days later, a  timer job(can't recall which one at the moment) deletes/cleans  the groups belonging to the site and the site becomes inaccessible (somehow assuming they are no longer needed...)

    They confirmed this is a bug and said they'll release a fix but I am not working in that place anymore, and don't know if they've released the hotfix yet!


    ceren




    • Modifié ova c jeudi 21 mars 2013 15:35
    jeudi 21 mars 2013 15:30
  • Hi sorry for not posting for a while. Microsoft came back to us and said that this issue is a bug.

    what happens is you have a site and you copy this site using export/import with the include security option. Then you delete the old site and the imported site remains there. 30 days later, a  timer job(can't recall which one at the moment) deletes/cleans  the groups belonging to the site and the site becomes inaccessible (somehow assuming they are no longer needed...)

    They confirmed this is a bug and said they'll release a fix but I am not working in that place anymore, and don't know if they've released the hotfix yet!


    ceren




    Ceren,  I'm not sure if that is what you dealt with, but we did not do any importing/exporting of sites.  The issues just simply happened.

    I have not had it happen through several Windows Updates or SP2010 CU installs since October, so I'm not sure what the trigger is.

    vendredi 22 mars 2013 15:37