Manually remove user from Profiles Missing from Import

Due to some requirement we need to disable the My Site Cleanup Timer Job , then we need to do manually clean up for those user is leaving organization.

Below is the script we can remove the user from user Profile (Profiles Missing from Import).

The reasons we remove it because we don't want other user click on the user leaving organization rout to user profile , want it point to user table information at site collection database.

More detail here :

https://blogs.msdn.microsoft.com/kaevans/2012/06/25/inside-the-sharepoint-2010-my-site-cleanup-timer-job/

========================================================================

if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}
$siteUrl = "xxxx"
$site = Get-SPSite $siteUrl
$context = Get-SPServiceContext($site)
$site.Dispose();
$pm = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context); 

$ProfileDB = Get-SPDatabase | ? { $_.Type -eq “Microsoft.Office.Server.Administration.ProfileDatabase”}
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ProfileDB.DatabaseConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'select NTName,RecordId from UserProfile_Full where bDeleted=1'
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close();
Write-host 'TotalCount: ', $DataSet.Tables[0].Rows.Count

Write-Host 'Following Inactive Accounts will be deleted !'
foreach($user in $DataSet.Tables[0].Rows)
{
   write-host 'Planning to delete :' $user['NTName'], ',', $user['RecordId'] -ForegroundColor Green
   $profile = $pm.GetProfile($user['RecordId'])
   write-host $profile.DisplayName;
    #To enable delete operation remove comment out for below line
    #$pm.RemoveProfile($profile)
    write-host $user['NTName'] is deleted!!! -ForegroundColor Red
}
write-host 'Operation Completed !'

========================================================================

How to find out which site collection is using RBS ?

$SPWebApp = Get-SPWebApplication https://abc.com

foreach ($SPSite in $SPWebApp.Sites)

{

    if ($SPSite -ne $null)

    {

        $site=Get-SPSite $SPSite

Ssite

$dbname = $site.ContentDatabase.name

$dbname

$rbss=$site.ContentDatabase.RemoteBlobStorageSettings

#$rbss

        $SPSite.Dispose()

    }

}

SQL script add user to all content database

SET NOCOUNT ON;

DECLARE @user_name    SYSNAME

        , @login_name SYSNAME;

SELECT @user_name = ‘Domain\Account’,

       @login_name = ‘Domain\Account’

SELECT '

    USE ' + QUOTENAME(NAME) + '

CREATE USER ' + QUOTENAME(@user_name)

       + '

    ALTER ROLE [db_owner] ADD MEMBER [Domain\Account]

    GO

 

'

FROM   sys.databases

WHERE  database_id > 4

       AND state_desc = 'ONLINE'

---------------------------------------------------------------------------------------------------

• execute the results to text then can copy and paste to run it 
• excluded the system database

SharePoint 2013 security hotfix remove the application pool permission from RBS content database DB owner

Question :
we notice after the security hotfix patch, application pool account is remove from the content Database DB owner (this setting is for RBS) , this is known issues ?

Answer :
Yes, after security hotfix Patch or any patch update, application pool account will be removed from the content Database DB owner for RBS enabled databases. We would need to add the account back as DB owner.

RBS enable for sharepoint second content DB

1. add pool account to the dbowner content database

2.run sql script crease RBS group

USE WSS_Content_RBS

if not exists (select * from sys.symmetric_keys where name = N'##MS_DatabaseMasterKey##')

create master key encryption by password = N'Admin Key Password !2#4'

USE WSS_Content_RBS

if not exists (select groupname from sysfilegroups where groupname=N'RBSFilestreamProvider')

alter database WSS_Content_RBS add filegroup RBSFilestreamProvider contains filestream

USE WSS_Content_RBS

alter database WSS_Content_RBS add file (name = RBSFilestreamFile, filename = 'F:\WSS_Content_staffhub_RBS_test') to filegroup RBSFilestreamProvider

 3.check blob folder created

4. Run at DB server for second content db

msiexec /lvx* WSS_Content_RBS.txt /i rbs.msi REMOTEBLOBENABLE=1 FILESTREAMPROVIDERENABLE=1 FILEGROUP=PRIMARY FILESTREAMFILEGROUP=RBSFilestreamProvider FILESTREAMSTORENAME=FileStreamProvider_1 ADDLOCAL=EnableRBS,FilestreamRunScript DBINSTANCE=

*use interface to setup

  

6.check will see rbs table

7.run powershell

$cdb = Get-SPContentDatabase WSS_Content_RBS

$rbss = $cdb.RemoteBlobStorageSettings

$rbss.Installed()

$rbss.Enable()

$rbss.SetActiveProviderName($rbss.GetProviderNames()[0])  #make sure is only one provider 

$rbss

8.upload file and check on RBS folder