Add-SPShellAdmin : Some or all identity references could not be translated.

Recently I experienced an error while trying to give SPShellAdmin access to an administrator. The admin was supposed to be added to a new content database in the SharePoint 2010 farm.

The steps I took are:

1. Opened SharePoint PowerShell and ran the following command to get the DatabaseID:

Get-SPContentDatabase

Copied the databaseID for the db I was going to add the user to.

2. Ran the following command to add the user:

Add-SPShellAdmin -UserName domain\username -database xxxxxx-c69-358e-4fc6-82a2-yyyyyyyy

3. Got the following error:

Add-SPShellAdmin : Some or all identity references could not be translated.
At line:1 char:17
+ Add-SPShellAdmin <<<<  -UserName domain\username -database xxxxxx-c69-358e-4fc6-82a2-yyyyyyyy    + CategoryInfo          : InvalidData: (Microsoft.Share…AddSPShellAdmin:
   SPCmdletAddSPShellAdmin) [Add-SPShellAdmin], IdentityNotMappedException
    + FullyQualifiedErrorId : Microsoft.SharePoint.PowerShell.SPCmdletAddSPShe
   llAdmin

Well that the long and short of it tried all sort of combinations for try to get past the error but did not want to run the stsadm command as suggested by another post.

Resolution

So here is the bit where I had to find an alternate solution. Remeber the user was already an aministrator and wanted to ShellAdmin access to the new content database.

1. Connect to the SQL Server that is hosting the content database.

2. Navigate to Security folder > Logins > Right click and Add the user if not user does not already exisit.

3. If the user exisits under Logins > RIght click the userID and select properties > Under User Mapping > Select the content Database and select “Public” role > Click on OK. Do not select “SharePoint_Shell_Access” at this stage.

4.  Expand the SharePoint content database > Exapand Security folder > Expand Users folder > Right click and select Properties of user > Under Database role membership check “SharePoint_Shell_Access” and click OK.

5. Open PowerShell and type the command:

Get-SPShellAdmin -database  xxxxxx-c69-358e-4fc6-82a2-yyyyyyyy 

You should not get the new user listed under the Shell Admin.

Drop a feedback if this has saved your life. Always good to hear from the SharePoint world.

PowerShell Move (Move-Item) files to designated folders based on CSV file

Recently I had to move over 5000 documents to their respective folders.

Scenario:

There there two columns in a csv file. First column contains folder name and second column the filename. The files had to be moved to individual folders. There can be multiple documents per folder. You need to ensure that the folders have been created in the $dir location.

#PowerShell script to create folder

Set-Location “D:\Export\Files”

$Folders = Import-Csv D:\folders.csv -Header folders

 ForEach($Folder in $Folders) {

 New-Item $Folder.folders -type directory

}

 #Here is the PowerShell script for moving the files in required folder:

$text = Import-Csv “D:\InputFile.csv” 

$dir = Get-ChildItem D:\Export\Files

 foreach($item in $text ){

 foreach ($folder in $dir) {

#where Package is the name of the column in the csv file and FileName the name of #second column in the input csv file

 if($item.Package – eq $Folder.Name){

 $sourcefile = “D:\Export\” + $item.Filename 

$target = “D:\Export\Files\” + $folder.Name

 Move-Item$sourcefile$target

Write-Host“Folder $item.FileName moved sucessfully to $target”-ForegroundColor Green

 }

}

}

NServiceBus replay error queue from remote server

Here is an approach for playing NServiceBus error queue from Remote server. Quite often this is required if the operation team does not have access to teh Messaging server or you have a Distributor setup. This setup uses references to ReturnToSourceQueue.exe tools that is supplied from NServiceBus.

Save the command below in a .ps1 file and run it from Remote server. Remeber you need to have permission to run PowerShell in local server and Read/Write permission to the Error qs in the remote server.

param (

[Parameter(Mandatory=$false, HelpMessage=‘-Computername – The Servername for the Powershell session.’)]

[string]$Computername=“YOURREMOTECOMPUTERNAME”,

[Parameter(Mandatory=$false, HelpMessage=‘-Errorqueuename – The name of the Error Queue to be replayed.’)]

[string]$Errorqueuename=“error”,

[Parameter(Mandatory=$false, HelpMessage=‘-Path – The path location to the ReturnToSourceQueue.exe.’)]

[string]$Locationpath=“C:\install\nServiceBus\tools”

 );

##### check the the server, error queue name and Path exists #####

Write-Host “Checking Server: “$Computername;

Write-Host “Checking Error Queue: “$Errorqueuename ;

Write-Host“Checking Path: “$Locationpath ;

##### creates a remote session on the Servercomputer #####

$sessiontem = new-pssession-computername$Computername;

##### change directory to the location of the ReturnToSourceQueue.exe #####

Invoke-Command -Session $sessiontem {cd“C:\install\nServiceBus\tools”};

##### invoke the command to call replay the error queue #####

Invoke-Command -Session $sessiontem {.\ReturnToSourceQueue.exe error all};

##### close pssession #####

Remove-pssession $sessiontem;

##### completed #####

Write-Host $Errorqueuename“queue replayed successfully.” ;

SharePoint Config database Log file too big – Steps to reduce it

Ever faced the uphill task to manage large (over 50 GB at times) of SharePoint configuration database log file (can be MOSS 2007, SharePoint 2010 or SharePoint 2013). I have and to the extent that the Logfile might end up eating all the drive space.

Different people have different opinion on how to approach this and I have my own. Here goes my 2 cent:

1. Connect to the Configuration database SQL Server.

2. Run the following command against the configuration database (SP_config_XXXX):

DBCC SQLPERF(logspace)

The above command will show you the amount of free space available. It generally will be 99% used. Issue a checkpoint now. Run:

DBCC SQLPERF(logspace)

Checkpoint

3. Take your Transactional logfile backup using SSMS:

Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio > Connect to the Configuration database SQL Server > Right Click the Config database name > Tasks > Backup > Under Backup Type Select “Transaction Log” > Provide a Backup to location > Under Options make sure “Truncate the transaction log” radio button is selected > Set Backup compression select “Compress backup”

Wait for it to finish may take 5 -7 minutes but depends on server resources available.

4. Once Step 3 is complete Run :

DBCC SQLPERF(logspace)

You should see that the logspace used % for the config db is now  0.7% or so.

5. Issue the command to find the Config database logfile name:

Select * from Sys.Database_Files

Copy the name of the Configuration database logfile cause you are going to need it in the next step.

6. Shrink the logfile now using the command below. My Logifile name is SP_Config_prod01_log and I used 1000 MB since I did not want my logfiles to be 512 KB in size and then grow rapidly (I hate illusion)

DBCC ShrinkFile (‘SP_config_prod01_log’,1000)

7. Now the fun starts. You will be surprised to note ladies and gentlemen that the logfile has not shrunk. Indeed we live in a world of illusion. At this point I will request you to maintain peace and not run like a headless chicken and follow these steps.

Redo Step 3 to take the Transactional logfile backup again and this time under Options in the backup menu > under Overwrite media > Select “Append to the exisiting backup set”.

This time the backup will be much faster than Step 3.

8. Issue the Shrink command again:

DBCC ShrinkFile (‘SP_config_prod01_log’,1000)

9. Check the Log Size using:

DBCC SQLPERF(logspace)

The Config DB logfile size should have gone down to 1000 MB.

10. And the most important. Thank God and not me that you reached this far without screwing up your production server.

PowerShell script to create many SharePoint sites in a site collection

All you need to change is the Site Title (@Subsites) and the Site Collection URL ($SiteCollectionURL):

Write-Output ” “
Write-Output “Creating Sub Sites”

$SiteCollectionURL = “http://yoursharepointsite/sitecollection/01″

$SiteCollectionTemplate = “SiteDefinitionTemplate#0”

$SiteCollectionLanguage = 1033

$SubSites = @(“Site Titel 1”, “Site Title 2”)

for($i=0 ; $i -lt $SubSites.count ; $i++)
{
$SiteUrl = “”
$SiteUrl = $SiteCollectionURL + “/”
$SiteUrl = $SiteUrl += $SubSites[$i]
Write-Output ” “
#Write-Output “Creating Site for ” += $SubSites[$i]
Write-Output ” “
New-SPWeb $SiteUrl -Template $SiteCollectionTemplate -Name “Your Site Name:” $SubSites -UseParentTopNav -Language $SiteCollectionLanguage -Description “Document Library description” $SubSites
Write-Output ” “
#Write-Output “Site Created for ” += $SubSites[$i]
Write-Output ” “
}

PowerShell to Update Created By and Modified By entries in SharePoint List or Document Library

[Reflection.Assembly]::Load(“Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c”)

$SPSite = New-Object Microsoft.SharePoint.SPSite(http://yoursitename/site1/site2/)

$SPWeb = $SPSite.OpenWeb()

$SPList = $SPWeb.Lists[“Your List”]

$SPListItemCollection = $SPList.Items

foreach ($ListItem in $SPListItemCollection)

{

$SPFieldUserValue=New-Object Microsoft.SharePoint.SPFieldUserValue ($SPWeb,10, “domainname\username”)

$ListItem[“Editor”] = $SPFieldUserValue$ListItem[“Author”] = $SPFieldUserValue$ListItem.Update()

}

$SPWeb.Update()

Count any type of document in your SharePoint farm using PowerShell

Have you experienced a scenario when you have multiple SharePoint content databases across multiple SQL Servers. The scenario I had was following:

1. 3 SQL Servers.

2. Each SQL Server hosts multiple content databases

I did not want to write a script with the name of all the content databases but my script should pickup any SQL Server content database with the word “contet” in the database name. I save the SQL Server to a local file and call the query from PowerShell. In the script there are 2 variables defined, 1 the queryfile and 2 the SharePoint SQL Server instances. Here I am finding the total number of PDFs in my farm but you can use it to find any type of document. The script is as below (rememeber you need Farm Admin access and also need access to SQL Servers):

Save the script below as DocumentCount.ps1 and remember to save the SQL script to your desired location.

#Script starts here

$spqueryfile = “C:\SPDBQuery.sql” 

$sharepointDBinstances = @(“Server\SQLInstance1”,“Server\SQLInstance2”,“Server3\SQLInstance3”)

 $Final = 0 

foreach($sharepointDBinstance in $sharepointDBinstances){

 $initialresult= Invoke-Sqlcmd -ServerInstance $sharepointinstance -InputFile  $spqueryfile

 if (!$initialresult.HasErrors -eq $true){

 $Qresult = $initialresult | %{$_.Count} 

$Sum = $Qresult -join ‘+’ 

$Totals = Invoke-Expression $Sum

 $output = “total PDFs in your SharePoint Farm”

    $Final = $Totals + $Final

 }

}

Write-Host “The $output has value $Final”

#End of PowerShell script

The SQL Query is, you can change the query to report of doc or xlsx etc:

–Start of SQL Script

DECLARE @DBNAME VARCHAR(100), @STMT NVARCHAR(MAX)
DECLARE DBNAME_CURSOR CURSOR READ_ONLY
 for select name from sys.databases
   where name like (‘%content%’)
OPEN DBNAME_CURSOR  
FETCH NEXT FROM DBNAME_CURSOR into @DBNAME
WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @STMT = ‘SELECT COUNT(*) as Count FROM ‘ + @dbname + ‘.[dbo].[AllDocs] (NOLOCK) WHERE Extension = ”pdf”’
  EXEC sp_executesql @STMT
 FETCH NEXT FROM DBNAME_CURSOR into @DBNAME
 END
 CLOSE DBNAME_CURSOR
  DEALLOCATE DBNAME_CURSOR

 —End of SQL Script

Hit like if you like what you see…..

PowerShell to get SharePoint 2010 field name from site collection

param
(
 [Parameter(Mandatory=$true, HelpMessage=’-Url http://sharepointurl/site/test – Please provide the Url of the Site.’)]
 [string]$Url=””,
 [Parameter(Mandatory=$true, HelpMessage=’-FieldName – Please provide the Internal Field Name to remove.’)]
 [string]$FieldName=””,
 [Parameter(Mandatory=$true, HelpMessage=’-FieldType – Please provide the Field Type to remove.’)]
 [string]$FieldType=””
 
);

$site = Get-SPSite -Identity $Url -ErrorAction SilentlyContinue;
if($site -ne $null)
{
 Write-Host “Checking Site Collection:” $Url “Field:” $FieldName “….”-NoNewline;
 $checkSiteColumn = $site.RootWeb.Fields | ?{($_.InternalName -eq $FieldName) -and ($_.TypeAsString -eq $FieldType)};
 if($checkSiteColumn -eq $null)
 {
  Write-Host “Field Not Found!” -ForegroundColor Yellow;
 }
 else
 {
  Write-Host “Field Found” -ForegroundColor Green;
 }
 
 foreach($checkWeb in $site.AllWebs)
 {
  $caseFileList = $checkWeb.Lists.TryGetList(“Case Files”);
  if($caseFileList -ne $null)
  {
   Write-Host “Checking CaseFiles DocLib in” $checkWeb.ServerRelativeUrl “…”-NoNewline;
   $checkField = $caseFileList.Fields | ?{($_.InternalName -eq $FieldName) -and ($_.TypeAsString -eq $FieldType)};
   if($checkField -eq $null)
   {
    Write-Host “Not Found!” -ForegroundColor Yellow;
   }
   else
   {
    Write-Host “Found!” -ForegroundColor Green;
   }
  }
 }
 
}
else
{
 Write-Host “Site: ” $Url ” Not Found” -ForegroundColor Yellow;