My Sites and The SPDataAccess SQL Role

You’ve been busy spinning up your RTM SharePoint 2013 farms haven’t you? And of course, you’ve been deploying under the least-privileged security model like any good IT Pro. After you have everything configured, you open the event viewer and what to your wondering eyes should appear? An error of course!

The error and why

So, why is my content Application Pool attempting to access My Sites? Well, if we read Account permissions and security settings in SharePoint 2013 (go ahead, I’ll still be here when you finish) under the SharePoint service application accounts section (specifically My Sites application pool account) there’s a little follow-section titled Other application pool accounts. Let’s take a look at the permissions our “Other” pool accounts should be granted automatically:

Configuration Item Works as advertised
This account is assigned to the SP_DATA_ACCESS role for the content databases. No*
This account is assigned to the SP_DATA_ACCESS role for search database that is associated with the web application No**
This account must have read and write access to the associated service application database. Yes
This account is assigned to the WSS_CONTENT_APPLICATION_POOLS role that is associated with the farm configuration database. Yes
This account is assigned to the WSS_CONTENT_APPLICATION_POOLS role that is associated with the SharePoint_Admin content database. Yes

* The account is not automatically added to the My Sites content database(s). If it was, we wouldn’t be here.
** The search databases don’t have the SPDataAccess role.

Fixing the error

To alleviate the error (and make your servers happy again!), we’ll be heading off to SQL and executing a quick query on each content databases associated with your My Sites web application:


Microsoft has completely removed the BACKUP LOG WITH TRUNCATE_ONLY capability completely from SQL Server 2008. If you try to attempt to execute the following command:


This is the result:


To circumvent this error in a DEVELOPMENT environment (not Production), use the following commands:

USE AdventureWorks
DBCC SHRINKFILE(N'AdventureWorks_log', 1)

One should never execute the above commands in a Production environment unless you wish to lose all of your prior transaction log backups and the ability to restore to a given point in time based on those backups.


Tags: SQL sql server

Trimming MOSS Audit Logs

The Audit Log in MOSS has the potential to become unruly if left to it’s own devices. If you have upgraded to at least the Infrastructure Update, a new stsadm command is available for trimming the audit logs (specifically the AuditData table in the content database). The command is trimauditlog.

Sample Usage:

stsadm -o trimauditlog -date 20090101 -databasename MyContentDB

If you have auditing enabled and find yourself curious about the amount of space a particular table is consuming, the following script can help in finding the largest tables in a database:

Credit: Finding the biggest tables in a database

For more information, see Trimauditlog: Stsadm operation (Office SharePoint Server).

Exception: The content type is in use

I’ve found that working with InfoPath is becoming more and more of a chore.  A form I’ve been working on has had its promoted columns changed a number of times (Side note: I would just love to have a defined specification).

Today when I attempted to completely retract my form (inlcuding manually deleting the content type associated with the form), I was presented with the always helpful error “The content type is in use”.  This is by far one of the more helpful errors you will ever see from SharePoint.  Unfortunately it isn’t clear how to discover where the content type is actually in use.  A quick trip to the content database for the site collection you are working in can show us:

DECLARE @ContentTypeName nvarchar(128)

SET @ContentTypeName='Content Type Name Here'

SELECT w.Title AS [Web Site], w.FullUrl AS [Web Url], al.tp_Title AS [List Title], 
FROM ContentTypes ct1 
    JOIN ContentTypes ct2 ON LEFT(ct2.ContentTypeId, Len(ct1.ContentTypeId))=ct1.ContentTypeId 
    LEFT OUTER JOIN dbo.ContentTypeUsage ctu ON LEFT(ctu.ContentTypeId, Len(ct2.ContentTypeId)) = ct2.ContentTypeId 
    LEFT OUTER JOIN dbo.AllLists al ON ctu.ListId = al.tp_Id AND ctu.WebId=al.tp_WebId 
    LEFT OUTER JOIN dbo.Webs w ON al.tp_WebId = w.Id WHERE ct1.ResourceDir=@ContentTypeName

[Credit to Curtis Ruppe]

Once we know where the content type is in use, deleting it through the GUI is a trivial matter.

When you drop the default database of the farm Service Account…

When cleaning up a number of older databases in a QA/Staging environment (databases dating back to 2003), we managed to delete the default database for the SharePoint database system account. After deleting the database, the user will longer be able to perform actions on the SQL instance (login, view databases, etc.) when logging on through Management Studio.  To fix this error, log on to your SQL server as another administrative user (like the “sa” account), and execute the following:

sp_defaultdb 'SQLSERVER\dbuser', <DEFAULT DB> 

Replacing <DEFAULT DB> with the new default database name.