Requirement: Set content database to read only in SharePoint 2013 using PowerShell. Solution: We may have to set SharePoint databases to read-only mode in scenarios such as: upgrades, disaster recovery / highly available maintenance, patching, etc. Setting SharePoint content database in read-only mode from SQL Server Management Studio is explained in my another article: How to make SharePoint database read only . This time, Lets make content database read only using PowerShell. Tips: Use Get-SPContentDatabase | Select Name to get all SharePoint content databases! PowerShell to set content database read only SharePoint Prerequisites: Make sure you have SQL Server Management studio installed in your server/workstation to use below script! #You may have to adjust the below path according to your SSMS version! Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" #Configuration variables $SQLInstance ="Abjsvrv9&q
Requirement: Move SharePoint content database from one drive to another in SQL Server! By default, SharePoint creates its databases in the default SQL Server location: "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" folder (SQL Server 2016). We don't have any option from SharePoint side to specify where the data and log files for the database to be created. In my scenario, our SQL Server uses SAN storage and have a different drives, Say: E:\ and F:\ and we wanted all data files to go to E:\ and LOGS into F:\ Important: As always, Backup your databases before performing any of this activity! Solution: In short, Stop SharePoint services, Detach the database from SQL Server, Move database files to a different location, Attach the database back and then start SharePoint services! moving SharePoint content database files to new location can be performed by these steps: Step 1: Stop All SharePoint Services Lets move SharePoint content databas
Problem: Prior to SharePoint 2013, Members group of the site has "Contribute" permission. From SharePoint 2013, there was a new permission level "Edit" introduced with more rights. This introduced additional issue like members can delete lists and libraries! Solution: Remove Edit permissions and add contribute permissions to all users and group of the site! Here is how: Navigate to: Site Settings >> Site permissions Select the person or group with edit permissions, you want to change >> Click on "Edit User Permissions" ribbon button In Edit Permissions page uncheck "Edit" permission and select "Contribute" Click "OK" to save changes. Now the Members group has contribute permissions instead of Edit. Editing the "Edit" permission level and removing "Add, Edit and Delete Lists" permission from it - also solves the problem, but its not recommended to change OOTB permission levels in Sh
Requirement: Remove a Site Column from Content Type in SharePoint Online. How to Remove a Column from Content Type in SharePoint Online ? When you remove a site column from a content type, It does not delete the column from SharePoint Online. It simply removes the site column's association from the particular content type's definition. To remove a column from content type, follow these steps: Go to Site Settings >> Site Content Types (If its List level, Go to List Settings) Pick the content type from the page, In the columns section, Click on the column to remove from the content type. In the change content type page, click on "Remove" button and confirm the prompt to delete the column from the content type. SharePoint Online: Remove a Column from Content Type using PowerShell Here is how to remove a column from content type using PowerShell #Load SharePoint CSOM Assemblies Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web
Requirement: Remove a field from SharePoint list content type in SharePoint Online. PowerShell to Remove a Column from List Content Type in SharePoint Online: #Load SharePoint CSOM Assemblies Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" Function Remove-ColumnFromListContentType() { param ( [Parameter(Mandatory=$true)] [string] $SiteURL, [Parameter(Mandatory=$true)] [string] $ListName, [Parameter(Mandatory=$true)] [string] $ContentTypeName, [Parameter(Mandatory=$true)] [string] $ColumnName ) Try { $Cred= Get-Credential $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password) #Setup the context $Ctx = New-Object
Problem: During SharePoint 2013 migration, we ran Test-SPContentDatabase cmdlet and the output report contains several MissingAssembly errors! Category : MissingAssembly Error : True UpgradeBlocking : False Message : Assembly [DealEventEventhandler, Version=1.2.6.0, Culture=neutral, PublicKeyToken=b4122bae67581526] is referenced in the database [wss_content], but is not installed on the current farm. Please install any feature/solution which contains this assembly. Remedy : One or more assemblies are referenced in the database [wss_content], but are not installed on the current farm. Please install any feature or solution which contains these assemblies. Root Cause: Usually the MissingAssembly errors arises on event receiver which are not installed & deployed to the farm and still referenced in the SharePoint list or libraries! Solution: Install the missing feature/solution which contains the missing assembly to your SharePoint Farm! Si
Requirement: After the SharePoint migration from SharePoint 2013 to SharePoint 2016, decided to remove a broken web part from all pages wherever its being used. How to Remove a Web Part from Page in SharePoint? To remove a web part from page, you can edit the page and simply delete the web part. In case of page crash, use web part maintenance mode by appending "?contents=1" to the URL and then remove the unwanted web part. E.g. http://intranet.crescent.com/SitePages/Home.aspx ?contents=1 PowerShell to Remove a Web Part from All Pages in a Site Collection: Instead of deleting a web part from each single page from browser, we can leverage PowerShell to remove a particular web part from all pages in a site collection. Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue #Configuration parameters $SiteURL = "http://intranet.crescent.com" $WebPartTypeName="*BambooCalendar*" #Get All Subsites in a site collection and iterate th
Requirement: Add a site column to content type in SharePoint Online using PowerShell. How to add field to existing content type in SharePoint? By default Title column gets added to the content type. However, you may want to add additional columns. Go to the SharePoint Online site where you want Add a column to site content type. Click on Settings >> Click Site Settings. In Site Settings, Click on Site content types under Web Designer Galleries section Select your target content type to modify. In site content types page, under Columns section, click on "Add from existing site columns" link. This takes you to a page where you can select any existing site column. Select the appropriate group and pick the site column you want to add, and then click Add to move the column to the Columns to add list. Lets see how to add column to content type in SharePoint Online using PowerShell. PowerShell to Add Site Column to Content Type in SharePoint Online: Lets
Problem: MissingWebPart Issues when running Test-SPContentDatabase cmdlet on SharePoint content database during SharePoint 2013 migration project. Category : MissingWebPart Error : True UpgradeBlocking : False Message : WebPart class [9a43cf43-4500-d748-6385-09e622f28c01] is referenced [3] times in the database [WSS_Content_Database], but is not installed on the current farm. Please install any feature/solution which contains this web part. Remedy : One or more web parts are referenced in the database [WSS_Content_Database], but are not installed on the current farm. Please install any feature or solution which contains these web parts. Root cause: MissingWebPart issue is because, the migrated SharePoint 2013 sites contains references to custom Web Parts which are not installed in the target Farm. Solution: Install & deploy the solution package (WSP) which contains the webpart to your new SharePoint 201
Problem: Getting "MissingSetupFile" errors when you run the "Test-SPContentDataBase" cmdlet on SharePoint content databases during a migration process. [MissingSetupFile] File [Features\Feature-folder\feature-artifact] is referenced [1] times in the database [SharePoint_Content_database], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [SharePoint_Content_database], but are not installed on the current farm. Please install any feature or solution which contains these files. Root Cause: In SharePoint migration, The source SharePoint 2010 environment sites contains references to some of the Feature files and those features are not installed in the target SharePoint 2013 Farm. Solution: Well, The resolutions is: Install & Deploy all solutions from the source farm to the destination farm. Your organization may have built & deployed several cus
As the name suggests, Content type defines type of the content such as item or document. In a real world scenario, A content type could refer to a invoice template or project plan document, etc. Content types provide a effective method of grouping relevant columns (metadata), document template, workflows and policies together and helps to standardize a template for reuse. Typically content types are created at the top level site as they are hierarchical in nature. In other words a content type created at root site gets inherited to all its subsites automatically. This saves lot of time as you need to create that content type only once and reuse it wherever required. How to Enable Content Types in SharePoint Online? Before adding content types to a list or library, We must enable the content type management in list or library settings. To enable content type in SharePoint Online list, Go to List >> List Settings >> Advanced Settings Set "Yes" for "A
Problem: In a SharePoint 2013 to SharePoint 2016 migration, Got this error message on testing the content database with Test-SPContentDatabase cmdlet to identify possible issues: "Cannot open database [Database Name] requested by the login. The login failed" Troubleshooting: If your databases are hosted on a different SQL Server or instance, other than the default SharePoint database server as its config database: Use -ServerInstance parameter with Test-SPContentDatabase cmdlet, because SharePoint always tries to find the database on the default database Server. E.g. Test-SpContentDatabase -name "WSS_Content_KM_New" -webapplication http://portal.crescent.com -ServerInstance "SQL001\SharePoint" Test-SPContentDatabase is running from the context of the SharePoint Farm Administrator account. So make sure the Farm Account has enough access to the content database! Go to SQL Server >> Security >> Under Logins right click on your Farm
Test-SPContentDatabase: Cannot open database [Database Name] requested by the login. The login failed.
Reviewed by Salaudeen Rajack
on
January 16, 2016
Rating: 5
Requirement: Hide a Column from Content Type in SharePoint Online. How to Hide a Column using Content types in SharePoint Online? Columns can be hidden at both content type level or list level in SharePoint. You can hide a column from appearing in list forms, such as: NewForm.aspx, EditForm.aspx, DispForm.asxp using content type. Enable the content type first by going to: Go to List Settings >> Advanced Settings >> Enable "Allow management of content types" check the box Once content types are enabled, you can hide any field of that content type. Go to List Settings again >> Under Content Types Click on Item content type (or whatever content type applicable) Click on Title column (or the column you wish to hide) under columns group >> Click on Hidden option. Now the specific column won't appear on any of the forms as listed above. You can also hide the column from "Views" too by removing them from list views. PowerShell
Microsoft Power Query is a free add-in for Excel - Part of PowerBI that provides self-service Business Intelligence features. Lets see how to extract SharePoint list data using Power Query for Excel. Step 1: Download & Install Power Query Add-in To start with, Download the Power Query Add-in for Excel from: https://www.microsoft.com/en-us/download/details.aspx?id=39379 Once installed, You'll find a new tab "Power Query" tab added to Microsoft Excel. Step 2: Connect to SharePoint List To extract SharePoint list data, Click on "From Other Sources" button from the ribbon and choose "From SharePoint List" Provide the URL of your SharePoint site and choose Authentication method accordingly - I Selected Windows. For Office 365, choose organizational credentials. Once you are successfully logged in, You'll get the Navigator page to choose your source list and click on "Load". By default, Only Lists appear in the Navigator. Pic
Requirement: Find the last login time of all SharePoint users of the farm to find out inactive users. Solution: We can retrieve the user's last login time from Active directory (if the authentication provider is AD) using PowerShell. Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue Import-Module ActiveDirectory $UserAccount="vani" $User = Get-ADUser -Filter {SamAccountName -like $UserAccount} | Get-ADObject -Properties lastLogon $LogonTime=[DateTime]::FromFileTime($User.LastLogon) Write-host $LogonTime Lets add some more error handling and find last logon time of all users of SharePoint. PowerShell Script to Find the SharePoint user last login time: Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue Import-Module ActiveDirectory #Custom Function Get the Last Logon Time of the User from AD Function Get-ADUserLastLoginTime($UserAccount) { Try { #Set the Error Action $ErrorActionPreference
Web Applications are the top level containers in SharePoint. Each Web Application we create in SharePoint central administration is associated with site in IIS. How to get all web applications in SharePoint farm? To get a List of all available web applications in SharePoint, Navigate to: SharePoint 2016 Central Administration >> Application Management Manage Web Application Link. How to get all web applications SharePoint using PowerShell: Get-SPWebApplication cmdlet retrieves all the Web Applications in the Current farm. Get-SPWebApplication The above PowerShell cmdlet gives DisplayName, Url of all SharePoint web applications.To get a specific web application, use: Get-SPWebApplication http://yourwebappURL.com You can Iterate through web applications in PowerShell, as: #Get all web applications sharepoint using powershell $WebAppColl = Get-SPWebApplication #Iterate through each web application Foreach ($WebApp in $WebAppColl) { $Webapp.Url } #Or a one lin
Problem: During a production SharePoint site cloning into UAT using Database attachment method, after attaching the content database to the web application in UAT environment, found the current current number of site collections is 0 (zero). All site collections missing! Tried "View All Site Collections" from SharePoint 2013 Central Administration site, found nothing! No site collections listed. Troubleshooting: As a first step in troubleshooting, installed patches to the UAT environment to bring it on the same patch level. Tried upgrading the database: Upgrade-SPContentDatabase <database_name> Tried refreshing the site map in the config db: $Database = Get-SPContentDatabase -Identity "Database_Name" $Database.RefreshSitesInConfigurationDatabase() None of the above helped! Finally, tried running Test-SPContentDatabase and found the answer! Test-SpContentDatabase -name "WSS_Content_KM_New" -webapplication http://intranet.crescent.com -Ser
Requirement: Find all customized InfoPath list forms in SharePoint. Tasked to report a listing all the InfoPath List forms customized in SharePoint web application during a migration project. PowerShell Script to Find All InfoPath List Forms in SharePoint Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue #Configuration parameters $WebAppURL="https://intranet.crescent.com" $ReportOutput="C:\InfoPath-ListForms.csv" #Array to Hold Result - PSObjects $ResultColl = @() #Get All Webs of the Web Application $WebsColl = Get-SPWebApplication $WebAppURL | Get-SPSite -Limit All | Get-SPWeb -Limit All #Iterate through each web Foreach($Web in $WebsColl) { #Get All Lists with InfoPath List Forms in use Foreach ($List in $web.Lists | Where { $_.ContentTypes[0].ResourceFolder.Properties["_ipfs_infopathenabled"]}) { Write-Host "Found an InfoPath Form at: $($Web.URL), $($List.Title)" $Result = new-o
How to Import a Term Set from CSV file in SharePoint Online? Here is how to import term sets from a CSV file. Open SharePoint Online Admin center as Tenant administrator or SharePoint online Admin. (E.g. https://yourdomain-admin.sharepoint.com) Click on the "Term Store" link on the left navigation menu. On the Term store Management page, under General tab, click on "View a Sample Import File" link in "Sample Import" section. This gets you a sample CSV file. Open it in Excel, Populate the file as per your requirement. Once done, Import the CSV by clicking "Import Term Set" from any of the Term Set Group. Browse and locate the updated CSV file and click "OK" to start importing term set. That's all. Now your term store data should be updated from the CSV file.
Requirement: Remove User from Group in SharePoint Online using PowerShell SharePoint Online - How to Remove a User from Group? Removing users from SharePoint Online security groups can be done in just few clicks. Follow these steps to delete users from a SharePoint Online group: Navigate to your SharePoint Online site where the group is used, Click on Site Settings gear >> Click on Site Settings from the menu. From the Site Settings page, Click on "People and groups" under Users And Permissions section. On the Left navigation menu, select the group from which you'd like to remove the users. Tick the check box next to the user(s) you want to remove. Click the Actions drop-down arrow >> Select Remove Users from Group. In the confirmation pop-up message box, Click OK to confirm the deletion. If you don't see the Actions menu, it's because you don't have permissions to edit members of that Group! SharePoint Online: Remove User from G
SharePoint Web applications (or any web site built on ASP.NET) uses Web.config file to store and retrieve web site configurations such as database connection strings, security, etc. web.config file is actually a XML file with .config extension, typically located at the root of your web application. At times, You may have to add/update web.config entries. we can automate changes to SharePoint web application's web.config file using PowerShell. This PowerShell script gives you the ability to update web.config file. PowerShell to Add entry in web.config's AppSettings section AppSettings section in web.config can be utilized to read/update configuration values for your SharePoint customization. Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue #Config Parameters $WebAppURL = "http://intranet.crescent.com" $KeyName = "LicenseKey" $KeyValue = "91e727402aa24e769e2779c155168e3" #Get the web application $WebApp = Get-SPWebA
Requirement: Move SharePoint databases to new SQL Server! You'll have to move SharePoint databases including all content databases, configuration databases and service application databases to an another SQL Server at least in a couple of circumstances: Upgrading SQL Server to new version. E.g. Upgrading SQL Server 2012 to SQL Server 2016. Upgrading Servers to new hardware, moving servers from data centers, etc. Moving from single standalone server instances to highly available cluster servers - AlwaysOn Solution: In a nutshell, Moving SharePoint database from one server to another can be achieved with following steps: Step 1: Stop All SharePoint Services: Step 2: Backup & Restore All SharePoint Databases to the new SQL Server Step 3: Change the SQL Alias to Point to the new SQL Server Step 4: Start All SharePoint Services Step 1: Stop All SharePoint Services: To start with moving SharePoint databases to new server, Stop all SharePoint related services on a
Reviewed by Salaudeen Rajack
on
January 29, 2016
Rating:
![Test-SPContentDatabase: Cannot open database [Database Name] requested by the login. The login failed.](https://2.bp.blogspot.com/-fXAqSgXD47k/WHyPyng6jsI/AAAAAAAAMCI/6Mys5aiV-fYb-4omJPp46NBNKs-nRnokACLcB/s72-c/test-spcontentdatabase-Cannot%2Bopen%2Bdatabase%2B%255BDatabaseName%255D%2Brequested%2Bby%2Bthe%2Blogin.%2BThe%2Blogin%2Bfailed.png)
