How To Change The ‘Created By’ Column in a SharePoint List Using SQL

Recently I ran into a list that contained many items that were created by various logon accounts.  For sake of simple example, let’s say these were various regional office locations, all with their own regional Active Directory logon account.  The company decided to rebrand their offices, and change the prefix of the logon account to reflect this new branding.   Again, for sake of example, let’s say one particular office account was DOMAIN\RED-REGION-1 and now they were being branded to DOMAIN\BLUE-REGION-1.

Without careful consideration of what this might mean for SharePoint, when these offices started logging in to SharePoint with their new ‘BLUE’ accounts to update/edit list items, they no longer could see any previous items, because they were not previously created by the ‘BLUE’ account.

This was a result of the Item-level permissions setup in the Advanced Settings of the SharePoint list that essentially only let users see and edit items that they originally created with the logon account that was used.  Item-level Permissions set in the following way:

  • Read Access – Read items that were created by the user
  • Create and Edit Access – Create items and edit items that were created by the user

So, how to resolve, without having users recreate all their list items manually?  Well, there is always more than one way to ‘skin a cat’ it seems with SharePoint.  My first thought was to just export the list to Excel, then log in as the new BLUE account, and then paste the values back into the list using the Datasheet View.  However, there was a calculated column that formulated the Month/Yr that the list item was created, and that essentially is a ‘Read-only’ field and won’t allow pasting a value into it when using Datasheet View.   This original needed to be retained, so this was not a viable option.

Doing some research, I found that there were some PowerShell scripts that appeared to exist to resolve this problem, but I only could find ones that would work best if all the items in the list were created by the same user, and then could replace using  ‘Foreach’.  Unfortunately, I had a list with many items, and created by more than 30 different user accounts.  Being under a time crunch, and not feeling my PowerShell mojo at the time, I felt that  trying to figure out a way to manipulate the PowerShell scripts to look for only certain items that were created by specific users, and change the ‘Created by’ and ‘Modified by’ columns, was more involved than I wanted to get at the moment.

So, relying on my SQL Database knowledge and skills, I new that there must be a way to locate these fields in the database tables, and then update them via query commands in SQL Server Studio Manager.   So after doing some research, and doing some testing in a development environment, I was able to come up with a solution.  Again, this was easier for me, based on the scenario and the time crunch I was under.

WARNING: I do not recommend doing this in your production environment.  Microsoft does not support directly updating SharePoint databases.  Also, as a safety net, I recommend that you make database backups of the content databases that you will be making modifications to.

Steps to Complete:

  1. First, make sure that the new DOMAIN\BLUE-REGION-1 account has logged into SharePoint.  This insures that the new account registered in the UserInfo table of the content database
  2. Open up SQL Studio Manager (either on the SQL server or remotely) and connect to the SharePoint SQL Server.  The user account you connect with needs to have enough rights to write to databases, but I just use an account that is Sysadmin.
  3.  First, we have to find the 2 tp_ID‘s that have been assigned to the RED and the BLUE accounts in the UserInfo table.  To do so, use the following query (the ‘CONTENTDB’ should be replaced with your content database name):
    use CONTENTDBNAME
    select * from UserInfo
    where tp_login like ‘%REGION-1%’
    (using the % wildcard without RED or BLUE will locate both tp_ID‘s for the tp_Login accounts that contain ‘REGION-1′ with one query rather than finding each separately)
    Then click on ‘Execute
  4. In the results pane, look for the tp_ID number assigned for both, and then reference the tp_Login column to make sure which tp_ID belongs to each login.  In this example, let’s say that RED is 1000, and Blue is 1001.
  5. Now, list and library data is stored in the AllUserData table, each list or library item that is created, also has its own tp_ID, but it is not the same as the user account tp_ID in the UserInfo table.  So, we now need to determine what the tp_Author (Created by) value is for the RED account that does match up with the tp_ID UserInfo value.  From a new query window again, execute the following:
    use CONTENTDB
    select * from AllUserData

    where tp_author = 1000
  6. The results of the query will be displayed, we now want to find the tp_GUID value which will be unique for each list item that needs to be modified.  CAUTION, all user data, for lists and libraries in the content DB, will be displayed where this user was an author.  Make sure to look at the tp_ListId in the results, and look only for the ones that apply to the list you are modifying.  I had to scroll to the right in the results and identify data that existed in the list.  Mine had a common nvarchar1 column that had an identifiable value unique to this list.  Left-mouse-click on the value in the tp_GUID column to highlight, and then right-mouse-click on the value and select ‘copy’.  We will paste this value in the next query.
  7. The final SQL piece will be to ‘update’ and ‘set’ the tp_Author (Created by) to the new BLUE tp_Author value (1001 for this example) for the tp_GUID list item.  To do so, execute the following:
    use CONTENTDB
    go
    update dbo.AlluserData
    set tp_Author = 1001
    where tp_GUID = ‘DDCCC764-BBF9-41D2-B294-95ED828ED63B’
    NOTE: Your tp_GUID will be unique – above is just for example
  8. In the results pane, you should see a response of ‘(1 row(s) affected)’, which confirms the update has been made.
  9. Test the modification, by now having the user login with the new BLUE account and they now should see the item originally created by the RED account.  If for some reason you also want to replace the ‘Modified by’ column, you can use the same query in step 7, but replace the ‘tp_Author’ with ‘tp_Editor’ and leave the rest the same, and execute again.

Now, for a very large list with many user accounts, this could be a daunting task, but for me it fit the bill, and got the outcome I was in need of in a very short time.   Again, use at your own risk!

Failed to read the acls for an item. Access is denied. (Exception from HRESULT: 0×80070005 (E_ACCESSDENIED))

Recently in one of my SharePoint 2010 PerformancePoint environments, I came across an event log with the following error:

Failed to read the acls for an item. Access is denied. (Exception from HRESULT: 0×80070005 (E_ACCESSDENIED))

 Although I didn’t notice any issues in the environment, I suspected that this was not normal and proceeded to resolve.  The following steps allowed me to eliminate the error from occurring any longer:

  1. From Central Administration, go to Application Management > Manage Service Applications  and then click on the Secure Store Service Application.
  2. Put a check in the box next to the Target Application ID used for PerformancePoint Service and then click on Generate New Key from the ribbon.
  3. Next, open the SharePoint 2010 Management Shell (PowerShell) and enter the following commands:
    PS> $w = Get-SPWebApplication -Identity [http://yourwebapplication]
    PS> $w.GrantAccessTo ProcessIdentity([domain\serviceaccount])
    This PowerShell command does three things: It creates a new user (or makes sure the service account has permissions) for the contentdb for this web application, it makes the user a member of the db_owner role, and it creates a new schema for the user.

In my case the service account appeared to have the DB rights it needed, but something wasn’t 100% right, but the powershell commands resolved.

Error Attaching Database After SharePoint 2010 Farm Restore

Recently I ran into an issue when attempting to restore a SharePoint 2010 Farm Backup of a single web application via Central Administration. The backup was from a different farm than what it was being restored to. The environment consisted of SharePoint 2010 Enterprise (SP1 + CU Feb 2012) and SQL 2008 R2 (SP1).

Upon completion of the restore, there was one error related to the content database restore for the web application that caused the restore to partially fail:

Object [NEW DATABASE NAME) (previous name: [OLD DATABASE NAME]) failed in event OnPostRestore. For more information, see the spbackup.log or sprestore.log file located in the backup directory.

Now, the database actually does get restored to the SQL content database server, but does not complete the database add to the web application. The first issue is that the database retains the old service accounts from the previous environment, and an ‘access denied’ occurs when the restore process tries to attach the database to the web application for the new environment.

The first step to resolving this was to go into the SQL Studio Manager on the SharePoint SQL box and grant the appropriate service accounts from the new environment DBO rights to the newly restored database.

After adding the service accounts DBO permissions to the new databases, my next attempt to add the content database to the web application (Central Administration >; Application Management >; Manage Content Databases >; Add a content database), produced the following error:

The attach operation cannot continue because another object in this farm already contains the same ID. Each object in a farm must have a unique ID. In order to proceed with the attach operation you must assign a new ID to this database. To attach this database with a new ID, use the Mount-SPContentDatabase command with the -AssignNewDatabaseId parameter. Note that if this new database and an existing database contain the same site collections, attaching this database will likely result in orphaned site collections due to conflicts between the two databases.

This basically points to an orphaned database issue. To resolve this, I needed to open up SharePoint 2010 Management Shell and use the following PowerShell:

$orphanedDB = Get-SPDatabase | where{$_.Name -eq “[NEW DATABASE NAME]“}
$orphanedDB.Delete()

Next, I used PowerShell (not Central Administration) to mount the content database to the new web application and also assign it a new database ID:

Mount-SPContentDatabase -Name [NEW DATABASE NAME] -WebApplication [WEB APPLICATION URL] -AssignNewDatabaseID

After the above was completed, I needed to then use Central Administration to update the Site Collection Administrators (Central Administration >; Application Management >; Change site collection administrators ), as they still had the old environment accounts assigned.

Once this was all completed, my web application was back up and in working condition.

SharePoint 2010 ULS Logs Empty – 0 KB

Recently I built a test environment and was in need to investigate a few issues in the ULS Logs.  However, to my dismay, I found that there was no information in them.  They were being created, but all had a 0 KB size.  As one would first attempt, I restarted the SharePoint 2010 Tracing service, and found no resolve.

As I looked at the service in compared to another working SharePoint 2010 farm, I found the service was running as ‘Local System’ on the working farm, and in my test environment it was running as one of the SharePoint service accounts.

I changed the ‘Log on as’ to ‘Local System’ for the SharePoint 2010 Tracing service, then restarted the service, and all was back in order and ULS logs were capturing data.