SharePoint: Permission Error When Attaching Existing Content DB

I recently backed up a content database from production and restored it onto a staging server. When attempting to add it to a new web application, I received an error related to SQL Server:

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.
The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.

The message box had the same message twice. The problem was that the SharePoint Admin account had the deny permissions checked. The easy fix was to open SQL Server Management Studio and modify the roles of the admin account on the restored content database:

 

 

Make sure that db_denydatareader and db_denydatawriter are unchecked. Once I unchecked those roles and clicked OK, I was able to attach the content database to my new web app.

 



 

Adding Klout to SharePoint: Klout Web Part

My vision for a Klout web part was to have a control that served two purposes. The first purpose would be to allow users to enter their Klout account (if one wasn't entered already) which would then get stored in their profile. The second would be to actually display the Klout Score in the web part if the account was previously entered.

The steps to performing this are as follows:

1. Create a Visual Web Part that loads an .ascx control
2. Create a control that has at least a text box and button for the user to enter their Klout account.
3. Have the button update the SharePoint User Profile with the Klout account (see previous post for info on the custom property)
4. Have the web part render the Klout Widget image if the user's profile contained a Klout account.

I have a step-by-step walkthrough of these tasks in my guide here.
The steps in the guide are based on SharePoint 2010, however, it is a similiar process for SharePoint 2007 and SharePoint Online (via Office 365).

There are also instructions contained in the guide on how you may access and download code that assists in integrating Klout with SharePoint.

If you found this useful, please help support my SharePoint and .NET user group (Philly SNUG) by clicking on the logo below.

SharePoint 2010: Removing GUIDs in Several Service Application Database Names

Introduction

I noticed a few months ago that Shane Young was on a mission to explain how to remove the GUID from the default database names that are generated for each service application in SharePoint 2010. His main post which contains links to specific items is available here.

I luckily ran into several service applications that make it easy to rename the database as a new database is created and configured.

Word Automation Services
This was the first one I found. Using the SharePoint Powershell cmdlet, Set-SPWordConversionServiceApplication allows you to create a new database:

Set-SPWordConversionServiceApplication -Identity "Word Automation Services" -DatabaseServer SP2010\SQLSharePoint -DatabaseName WordServices

Managed Metadata Service
The Managed Metadata Service works the same way:

Set-SPMetadataServiceApplication –Identity “Managed Metadata Service” –DatabaseName TermStoreDB

Business Data Connectivity Service
The BCS works similiar:

$bcs = Get-SPServiceApplication -Identity bc4399ed-a2e0-4397-bf07-cd3d207e630e

Set-SPBusinessDataCatalogServiceApplication –Identity $bcs –DatabaseName BCS_DB

NOTE: You can find the GUID by using Get-SPServiceApplication and finding the BCS Service in the listing.

 

Secure Store Service
The Secure Store Service is similiar yet different. The Set-SPSecureStoreServiceApplication needs the identity of the service application, so you need to get that first:

$sss = Get-SPServiceApplication -Identity bc4399ed-a2e0-4397-bf07-cd3d207e630e

Set-SPSecureStoreServiceApplication –Identity $sss –DatabaseName SecureStoreDB

NOTE: You can find the GUID by using Get-SPServiceApplication and finding the Secure Store Service in the listing.

Here's the tricky part, although the command lines above run and take a few moments to process, the new database is not created. The new database name is set within the properties but SQL Server knows of no such thing. The easy fix here is to view the Secure Store Service Application properties from within Central Admin and click OK. Even though you may not have changed anything from the UI, the database name will be processed and a new database will be created.

Since you need to visit Central Admin anyway, you may just want to skip the PowerShell part and go right into the properties to change the name there.


Conclusion
Each service application has its own way of doing things, however, some make it easy to change the database name and actually create a new instance in SQL Server. Once the new databases are created, you can drop the old databases with the GUID. You may need to restart your servers such that the old databases are no longer in use. If you found this useful, please help support my SharePoint and .NET user group (Philly SNUG) by clicking on the logo below.

InfoPath Holiday Poem

InfoPath with SharePoint 2010 Holiday Poem

Twas the night before deployment, when all through the farm,
Not a service was stirring, not even an alarm*.
The forms were all published to a network location with care,
In hopes that the Admin will map a drive there.

The secondary data sources were all snug in their connection,
While the Secure Store Service provided protection.
And the assembly all built, and placed in the cache,
Had just settled strong names with keys to match.

When out on the farm there arose such a clatter,
I sprang from the VM to see what was the matter.
Away to the Windows Server, I remoted like a flash,
Tore open the Services and threw up the GAC.

The XML in the manifest and the method it knows,
Gave access to instantiate the objects below.
When, what did my wondering eyes see in the tools,
But picker controls and several “If” rules.

With a little rule driver, so lively and gay,
I knew in a moment it was hacked from the SDK.
More rapid than eagles the Conditions they came,
With dialogs and Actions that called them by name!

“Is Blank! Is Not Equal To! Is not a URL!”,
Setting a field’s value was all blown to hell.
“Begins with! Contains! This Field Changes!”,
Submit will not work with values in these ranges.

As workflows that before the wild tasks did fly,
When they meet with an obstacle,  we just mount a new drive.
So up to the production server they flew,
With an XSN full of schemas, and a DLL too.

And then, in a twinkling, I heard on the prompt,
s-t-s-a-d-m someone did stomp.
I tried to be quiet, I tried not to yell,
“hey, now you can do that using PowerShell…”.

We added the template and with the .wsp,
Deployed the solution at a quarter-til three.
They asked if in a dilemma, what should they do?

Just pick up a copy of “InfoPath with SharePoint 2010 How-To”**!

Happy Holidays!!!

Steve Mann

www.SteveTheManMann.com

*another word for alert since alert doesn’t rhyme with farm

**Release Dates

Europe – Dec 3rd

USA and Japan– December 13^th

Canada – December 14^th

SharePoint 2010: Leveraging Visual Studio 2010 to Deploy Custom Content Types

lIt's fairly easy to create content types right within SharePoint itself. However, when in a development scenario, you need an easy way to replicate custom content type creation without having to manually enter site columns and site content types by hand. Luckily Visual Studio 2010 has a project for that. Generating  a Content Type project will easily package up site column and content type definitions into a re-distributable solution/feature allowing for seemless deployment between environments (e.g. dev, staging, uat, production, etc.).

The first step is to fire up Visual Studio 2010 and select File -> New -> Project. The New Project dialog appears:

In the New Project dialog  change the target platform at the top to .NET Framework 3.5.and select the Content Type template. Enter the appropriate name and location and click OK. The SharePoint Customization Wizard dialog appears:

The local site should be populated automatically but you may enter a different address if needed. Clicking on the Validate button will ensure the server is reachable. Change the trust level option to Deploy as a farm solution and click Next.

Select the type of base content type that your new content type should inherit. For document libraries, select Document, for lists, select Item. Click Finish.  The project is generated and displays the Elements.xml contents.

TIP: The Document content type actually inherits off of the Item content type. You may use the same type of hierarchical structure if you have a common custom content type that can act as a base for additional custom content types.

The content type structure is displayed but there are no fields. For each custom field you wish to create for the content type, you need a Field Definition. Therefore at the top of the Elements.xml  before the Parent Content Type comment but after the <Elements> tag, enter a field definition entry similar to the following:

<!-- Site Columns -->

<Field ID="{1B0D25D8-5A89-45C0-AE25-FA6726063EB2}" Name="CustomField1" DisplayName="Custom Field 1" Type="Text" Required="FALSE" Group="Custom Document Columns"/>

<Field ID="{3EE19A29-D064-4188-BFA1-BC23968BF1BC}" Name="CustomField2" DisplayName="Custom Field 2" Type="Text" Required="FALSE" Group="Custom Document Columns"/>

To create the ID, use the Create GUID utility which is accessible from the Tools menu. Select the Registry Format (option 4) and click the Copy button. Paste the GUID into the ID. Click the New GUID button for all subsequent columns (field defs). The Elements.xml should look similar to the following:

NOTE: The use of site columns is necessary!! Generating the fields as site columns allows you to use them in other content types.


The current state of the Elements.xml will generate the site columns but not place them into the content type. For that to happen, you need to enter Field Reference entries based on the site column definitions:

<FieldRef ID="{1B0D25D8-5A89-45C0-AE25-FA6726063EB2}" Name="CustomField1" DisplayName="Custom Field 1" />

<FieldRef ID="{3EE19A29-D064-4188-BFA1-BC23968BF1BC}" Name="CustomField2" DisplayName="Custom Field 2" />

     

Place these field reference lines within the <FieldRefs></FieldRefs>  tags. Your content type section should look like the following:

Rename the Content Type to a more meaningful descriptive name. Use the same name to rename the Content Type object in the project within the Solution Explorer pane as well as within the Project-level properties. Save the Elements.xml changes.

Double-click the Feature1 entry in the Solution Explorer pane and modify the Title and Description accordingly as well as the Feature1 item. Save the Feature1.feature file. Double-click the  Package.package file and change the name of the solution file. Save the Package.package file.

If you have a new list or library that will use the new content type you may want to include that within this solution such that they can be deployed together (see this post).

Right-click the Project in Solution Explorer and select Deploy:

The content type package is built and added to the SharePoint site as a farm solution. The solution is deployed globally as a feature that is available at the site level. The site feature will be activated at the root site collection but may not be activated within other sites. The site columns and content type are created accordingly on the root site.

SharePoint 2010: Patch Warnings in Health Reports

I recently recieved two warnings in Central Admin in regards to upgrades:

• Product / patch installation or server upgrade required
• Databases running in compatibility range, upgrade recommended.

I thought this was strange since I just applied the August 2010 Cumulative Hotfixes. After reviewing the details of the issues I realized that the SharePoint Product Configuration wizard needed to be run. The details explain about running psconfig or psconfigUI - don't be alarmed - psconfigUI is just a fancy technical term for the SharePoint Product Configuration wizard. Usually patches and updates run that automatically but I guess I missed the memo that you need to make sure it runs after applying the hotfixes. Anyway, it is an easy fix and I am always confident that the wizard is doing the right thing.

  

Microsoft SharePoint Server 2010 Bible Release

While online retailers have been shipping the book since last week, today is the official release of this awesome publication.

SP2010 Patches

The August 31, 2010 patches for SharePoint Foundation and SharePoint Server can be found at the following links:


http://support.microsoft.com/kb/2266423


http://support.microsoft.com/kb/2352342