none
MOSS 2007 - what timer job\action updates the tp_deleted column in the userinfo table

    שאלה

  • I am having an issue where a couple of users which have been created by our fba, are randomly being made 'inactive' in the userinfo table for a MOSS 2007 web application.  I can see that the tp_deleted column has been updated to make the user inactive but I need to find out what is causing the column to be updated?

    The action has not been made by a manual intervention so I am leaning towards it being a timer job that is changing the users to be 'inactive', has anyone experienced this before? I am understood that the profile synchronisation timer job doesn't update forms based users only AD user accounts, is this correct?

    Thanks

    יום רביעי 29 פברואר 2012 13:28

תשובות

  • Ok, I have discovered that the cause of the issue for us is our custom external logon page which was not trimming the username correctly.  The test which proved this was to set up our extranet environment to use the out of the box logon page which is automatically designed to trim whitespaces at the beginning and end of the username, which will then not create duplicate entries in the Userinfo table of the same name with additional whitespaces.

    You can change your extranet application to the OOTB logon page by changing the forms loginurl to point to Microsoft's default logon page as per the details below.  We are now re-writing our custom logon page based on the OOTB logon page to ensure that username trimming works.  This is our resolution.

    Approx Line 133 of the web.config:

    <authentication mode="Forms">
          <forms loginUrl="/_layouts/login.aspx" />
        </authentication>

    • סומן כתשובה על-ידי msmith-uk יום שני 23 יולי 2012 12:12
    יום שני 23 יולי 2012 12:12

כל התגובות

  • Hi MSSmith,

    So looks like I'm not unique on this issue - I've seen this few times in a similar environment you described. Found no clue what causing this. I created a thread here a while ago with no luck. In this meantime I've tried turning the auditing on which confimed this user is NOT getting removed from the site by any Admins.

    I'm curious what value you see on the tp_deleted field for those inactive users? For me I always see '60' assinged - not sure whehter that means anything. Also do you import user profiles from active directory?

    As for your question Profile Sync job should work in FBA site too. You can test this out deleting a test user in SSP - which should be marked inactive in the 'userinfo' table in a hour (when the time job runs). But in my case that particular user is active in SSP DB while being marked inactive in the site collection content Db.

    Hope this gives you some comfort (knowing there are other people out there too with the same pain) or frustration (as no known solution) :-)


    BlueSky2010
    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    יום רביעי 29 פברואר 2012 17:35
  • Hi BlueSky2010,

    It's good to know we aren't the only ones with this issue.  Have you made any progress with this issue? The value we are seeing in the tp_deleted column is the same value as the tp_ID column, this is the same on every occasion. Have you found a workaround for this issue?

    Have you tried to make the user active again by updating the tp_deleted and tp_IsActive columns? We are testing this process now. It does seem a little strange, but when you manually change the tp_deleted and tp_IsActive columns the user is still active in the site. I am presuming that the profile synchcronisation job would then remove the access to the site?

    We are considering deploying MOSS 2007 sp2 to see if this fixes the issue but we are going to try some more testing first.

    יום שישי 02 מרץ 2012 17:04
  • No - no solution no workaround :-(. Happened 3 times so far with the same user. Opened a Microsoft premier ticket with no luck.

    I have thought many time to update the table tp_IsActive column and even proposed to the MS support guy but he discouraged me (as this is only happening in Production - so could not test this out in DEV environment). In my case it's MOSS SP2.

    You importing users from Active directory, right? Nothing changes for these uses in AD site?  


    BlueSky2010
    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    יום שני 05 מרץ 2012 14:37
  • Ok, that's not so good. We were planning to open a Microsoft ticket aswell as we believe it's a bug in SharePoint. We are running Moss 2007 Sp1 and we were planning to go to sp2 and sp3 to see if that resolved the issue. Have you considered foing to Sp3?

    Yes, we are importing users from AD aswell as using the FBA and nothing changes for the AD users, only the FBA users. I was understood though that the profile synchronisation timer job would only update the AD users?

    יום שני 05 מרץ 2012 16:32
  • No - We aren't planning to upgrade to SP3 rather would upgrade to 2010. My understanding is for MOSS 2007 - Profile sync job syncs between your SSP and Content databases. So for your FBA users if you are importing their profile through SSP those should be synced into the content DB userinfo table as well. 

    How did your test go updating the UserInfo table's Tp_IsActive column? Does the user get all his permissions back too? If that is the case - it can be a workaround. :-)

     

    BlueSky2010
    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    יום שני 05 מרץ 2012 20:40
  • Why is this marked as an answer? Has anyone gotten ANY sort of answer to this issue?

    יום שלישי 12 יוני 2012 19:55
  • I currently have an open call with Microsoft on this issue and so far they haven't found a fix. The issue does look to be like a timer job making the user 'inactive' as entries in the 'userinfo' table and the 'auditdata' table show updates made by the ID of the system account (farm account). As soon as I get a fix from Microsoft I will update my post. In the meantime I have set up a daily powershell script to query the 'userinfo' table with users that have had the 'tp_deleted' column recently updated. This just helps to keep tabs on recent users that have been made inactive.
    יום שני 25 יוני 2012 14:06
  • Ok, update for you chaps which you might find interesting..

    I have managed to re-create the issue and now know why the users are suddeningly being deleted. The issue relates to the users typing an additional whitespace at the end of their username when they login.  SharePoint then creates a new profile in the content database table 'UserInfo' with no access and then marks the correct profile as deleted by populating the tp_deleted column.  To re-create the issue follow the steps below:

    1. Create a new username (e.g 'b.davis') in your membership database.

    2. Add the new external user 'b.davis' access to your SharePoint site. This will create a single profile in the content database with access (tp_isactive=1) which is correct.

    3. Extranet user then attempts to login with the username and password but types an extra whitespace at the end of the username e.g 'b.davis '. SharePoint then accepts this login but creates a new profile that doesn't have permissions to the required site.  Significantly, the correct profile is then marked as deleted.

    If you run the query (select * from [ContentDatabaseName].[dbo].[UserInfo] where tp_login like '%b.davis%' order by tp_id desc) you will see the duplicate profiles in the table with the tp_login and tp_title with the additional whitespace.

    Resolution: Working with Microsoft at the moment for a fix, but we are likely to implement an update to our extranet logon page so it will not accept whitespaces.

    יום רביעי 04 יולי 2012 16:32
  • Ok, I have discovered that the cause of the issue for us is our custom external logon page which was not trimming the username correctly.  The test which proved this was to set up our extranet environment to use the out of the box logon page which is automatically designed to trim whitespaces at the beginning and end of the username, which will then not create duplicate entries in the Userinfo table of the same name with additional whitespaces.

    You can change your extranet application to the OOTB logon page by changing the forms loginurl to point to Microsoft's default logon page as per the details below.  We are now re-writing our custom logon page based on the OOTB logon page to ensure that username trimming works.  This is our resolution.

    Approx Line 133 of the web.config:

    <authentication mode="Forms">
          <forms loginUrl="/_layouts/login.aspx" />
        </authentication>

    • סומן כתשובה על-ידי msmith-uk יום שני 23 יולי 2012 12:12
    יום שני 23 יולי 2012 12:12