When loading this page in Central Administration, I was being presented with a Excel Services error and a correlation ID.

Looking at the ULS logs I noticed the following error.

Insufficient SQL database permissions for user 'Name: <DOMAIN\USERNAME> SID: <SID-CODE> ImpersonationLevel: None' in database '<DATABASE-NAME>' on SQL Server instance '<SQL-INSTANCE>'. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object 'proc_ReturnWebFeatures', database '<DATABASE-NAME>', schema 'dbo'. 1d0d479c-5a36-c0dc-912b-5bc267b09a0a

The database name was referring to the Content Admin database of Central Administration and the user name was referring to the service application account that the PowerPivot service application had been configured to run under.

The proc_ReturnWebFeatures stored procedure required the user to be a member of the SPDataAccess role to allow EXECUTE permissions. The service application user was only in the WSS_Content_Application_Pools role.

If you run the following script, this will grant the correct rights in the database

1
2
3
$url = "[http://centraladminsite:2013](http://centraladminsite:2013)"
$webApp = Get-SPWebApplication -Identity $url
$webApp.GrantAccessToProcessIdentity("DOMAIN\serviceapplicationuser")

Now when you load up the PowerPivot Management Dashboard, you report should be displayed.

Comment and share

THIS IS VERY BAD. WE ALL KNOW THAT MAKING DIRECT UPDATES TO THE DATABASE WILL CREATE SOME SORT OF BLACK HOLE AND KITTENS DIE IN REDMOND ETC, ETC. I HAVEN’T TESTED THE LONG TERM EFFECTS AND ONLY DID THIS ON MY DEVELOPMENT ENVIRONMENT TO GET ME OUT OF A PICKLE.

Disclaimer over.

If you ever get this error when trying to update a field, either through the web UI or programmatic-ally it normally means your Version fields are out of sync. This can occur if your field has been created using the Schema XML from another environment or you have migrated content using some dodgy 3rd party app.

Run this update on your content databases and you all should be good again.

UPDATE [MyContentDatabase].[dbo].[ContentTypes] SET [Version] = CAST([Definition] AS XML).value('(/Field/@Version)[1]', 'int') WHERE CAST([Definition] AS XML).value('(/Field/@Version)[1]', 'int') <> [Version]

Comment and share

If anyone knows a better way then let me know.

1
2
3
4
5
6
7
8
9
10
11
string smtp = string.Empty;

SPFarm.Local.Servers.ForEach(s => s.ServiceInstances.ForEach(i =>
{
if(i is SPOutboundMailServiceInstance)
{
smtp = s.Address;
}
}));

return smtp;

I use a ForEach extension which I’ve included below

1
2
3
4
5
6
7
public static void ForEach<T>(this IEnumerable<T> enumerable, Action<T> action)
{
foreach(T item in enumerable)
{
action(item);
}
}

Comment and share

I have been trying to rebuild a development environment recently using DBA created tables.

Every time I ran the PSCONFIG -cmd configdb -create command it ran and then threw an error.

The error message above was logged.

After looking through the tables, I noticed that some of the tables in both the config db and the config admin db had been created under the user account I had been running the installation under and some had been created under dbo.

There are a couple of SQL scripts in the layouts folder under SQL that are run to create the schemas and here I think is the problem.

Most of the CREATE TABLE statements using the following syntax CREATE TABLE [dbo].[TableName], but there are a few tables in both databases that just use CREATE TABLE TableName.

The error occurs because further SQL script is then referencing [dbo].[TableName], but these tables don’t exist under the dbo user schema, so it errors.

The solution is to change the SQL scripts so all CREATE TABLE statements have [dbo]. infront of them. Before you run it again you will need to clear out all the objects from both the databases.

We have some rather quirky database rights in our environment, but the install account and the farm service account both had dbcreator and secuirtyadmin rights and were in the db_owner role for each of the databases.

Comment and share

I don’t know if anyone else has experienced this, but the Microsoft documentation provides this as an example for the above command

1
Psconfig –cmd configdb –create –server _servername_ –database _databasename_ –user _username_ –password _userpassword_ –admincontentdatabase _databasename_

I tried running this and kept getting the following error

1
The -create command is invalid

This drove me made until I removed the “-“ from the beginning of the create parameter and then I got the following error

1
The -server command is invalid

My amazing mind realized that a pattern was emerging and I went ahead and removed all the dashes from the parameters to end up with the following command.

1
Psconfig –cmd configdb create server _servername_ database _databasename_ user _username_ password _userpassword_ admincontentdatabase _databasename_

Hey Presto my “SharePoint Products and Technologies Configuration Wizard” began chugging away

Comment and share

  • page 1 of 1
Author's picture

Toby Statham

Independent Office 365 / SharePoint Specialist and an associate consultant at aiimi.com, an Information Management company.

Independent Office 365 / SharePoint Specialist

Brighton, UK