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:
- 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
- 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.
- 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):
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‘
- 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.
- 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:
select * from AllUserData
where tp_author = 1000
- 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.
- 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:
set tp_Author = 1001
where tp_GUID = ‘DDCCC764-BBF9-41D2-B294-95ED828ED63B’
NOTE: Your tp_GUID will be unique – above is just for example
- In the results pane, you should see a response of ‘(1 row(s) affected)’, which confirms the update has been made.
- 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!