SharePoint – Test-SPContentDatabase complains of Classic Mode database

The Issue:

 

Migrating SP 2010 classic content database to SP 2013 Claims web app. The Test-SPContentdatabase command complains that the database is still in classic mode.

 

Steps that were performed:

 

•    Created a new classic web app from PS - https://SPWEB:8080
•    Mounted the 2010 database.
•    Post this we followed steps on the TechNet - Migrate from classic-mode to claims-based authentication in SharePoint 2013 https://technet.microsoft.com/en-us/library/gg251985.aspx

        $WebAppName = "https:// SPWEB:8080"
        $wa = get-SPWebapplication $WebAppName
        $wa.UseClaimsAuthentication = $true
        $wa.Update()

        $account = "yourDomain\yourUser"
        $account = (New-SPClaimsPrincipal -identity $account -identitytype 1).ToEncodedString()
        $wa = get-SPWebApplication $WebAppName
        $zp = $wa.ZonePolicies("Default")
        $p = $zp.Add($account,"PSPolicy")
        $fc=$wa.PolicyRoles.GetSpecialRole("FullControl")
        $p.PolicyRoleBindings.Add($fc)
        $wa.Update()

        $wa.MigrateUsers($true)
        $wa.ProvisionGlobally()
        

•    Post this the test-SPContentDatabase was still showing inconsistency.
•    We then performed this cleanup : We wanted to remove any account that no longer existed in AD.
•    Ran the below PS script to identify inactive users in the site collection [by comparing them to AD].

 

        $web = get-spweb "https://SPWEB:8080/sites/sitecollection"
        $allusers = $web.siteusers
        $array = @()
        foreach($user in $allusers)
        {
        $login_full = $user.UserLogin
        $elements = $login_full.split("\")
        $login = $elements[1]
        $ds = New-Object System.DirectoryServices.DirectorySearcher
        $filter = "(&(objectCategory=User)(sAMAccountname=" + $login + "))"
        $ds.Filter = $filter
        if ($de = $ds.findone()) {write-host $login_full "User enabled"}
        else
        {
        write-host $login_full "User disabled"
        $array+=$login_full
        }
        }
        $array >path\fiIe.txt

        

•    From the output file, removed the SharePoint built in accounts and verified that the users are the ones missing from AD. Then fed the modified output file to the next script to remove such users:

 

        # Remove Pre-DefÌned Users
        $users = get-content -path "path\fiIe.txt"
        $web = get-spweb https://SPWEB:8080/sites/sitecollection
        $allusers = $web.siteusers
        foreach($temp in $ users)
        {
        write-host $temp "needs to be removed"
        remove-spuser -Id $temp -web $web -Confirm:$false
        }

 

•    Post this, we checked the output of the below SQL query : We wanted to check if there are any admin accounts still listed in the database using Classic Format.

 

SELECT TOP 100 [tp_SiteID],[tp_Login] FROM [UserInfo] WITH (NOLOCK) WHERE tp_IsActive = 1 AND tp_SiteAdmin = 1 AND tp_Deleted = 0 and tp_Login not LIKE 'i:%'

This returned two entries, which were causing the error :
tp_SiteID tp_Login
SiteID_GUID1 c:0+.w|SID1
SITEID_GUID2 c:0+.w|SID2

 

•    Using PowerShell, we figured that these SIDs were AD groups:

 

Domain\GRP1
Domain\GRP2

 

•    We removed these two groups from SharePoint and then the test-spcontentdatabase no longer complains about claims and classic inconsistency