Restoring a database into a different instance of tridion

Question

I have got most of the way but there seems to be a permissions issue somewhere:

Before the restore everything is working fine in my target environment - target has a server login account TCMDBUser which is mapped to my tridion_cm database user TCMDBUser

My source tridion_cm database has user TCMDBUser_DEV.

After restoring the source .bak into my target TCMDBUser_DEV is orphaned.

I edit the TRUSTEES table to correct MTSUser and my admin log accounts for my target environment and run the following to fix up my orphaned database user:

sp_change_users_login @Action='update_one', 
@UserNamePattern='TCMDBUser_DEV', 
@LoginName='TCMDBUser'
GO

I can log back in to Tridion explorer and see the expected list of publications and can walk through the tree structure but when I come to a folder which should contain items I see nothing with error:

and the corresponding event log error is:

Unable to get list of SDL Tridion Content Manager items.
DESCRIPTION

Error Code:
0x80040000 (-2147221504)

Call stack:
System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String)
System.Data.SqlClient.SqlDataReader.GetOrdinal(String)
System.Data.SqlClient.SqlDataReader.get_Item(String)
Tridion.ContentManager.Data.AdoNet.DatabaseUtilities.ConvertToFieldDictionary(IDataRecord,IDictionary`2)
Tridion.ContentManager.Data.AdoNet.IdentifiableObjectDataMapper.Read(TcmUri,IDataRecord,IDictionary`2)
Tridion.ContentManager.Data.AdoNet.ContentManagement.OrganizationalItemDataMapper.GetListItemsPost(IDataReader,TcmUri,OrganizationalItemItemsFilterData)
Tridion.ContentManager.Data.AdoNet.ContentManagement.OrganizationalItemDataMapper.Tridion.ContentManager.Data.ContentManagement.IOrganizationalItemDataMapper.GetListItems(TcmUri,OrganizationalItemItemsFilterData)
Tridion.ContentManager.ContentManagement.OrganizationalItem.GetListItemsData(OrganizationalItemItemsFilter)
Tridion.ContentManager.ContentManagement.OrganizationalItem.GetListItemsStream(OrganizationalItemItemsFilter)
Tridion.ContentManager.BLFacade.ContentManagement.OrganizationalItemFacade.GetListItemsXml(UserContext,String,ListFilter,ListColumnFilter)
Tridion.ContentManager.BLFacade.ContentManagement.OrganizationalItemFacade.GetListData(UserContext,String,EnumListKind,ListColumnFilter,String)
Folder.GetListItems

Answer 1

You will need to delete/drop the TCMDBUser_DEV form the DB and then create a new one with the same name and password (or reattach it to your cm DB). That should fix your problem.

I normally use the delete method with MS SQL server. I believe this occurs due to the ownership status that the TCMDBUser has on the database Schema.

When complete your TCMDBUser user should have the following permissions on your Tridion_CM database

Answer 2

Like Chris mentioned, I always drop the user from the database and then assign the existing TCMDBUser in SQL Server the rights to the restored database. You can drop the user with the following command (on the restored database):

EXEC sp_dropuser TCMDBUser

Then through the SQL Server - Security - Logins, you request the properties of your TCMDBUser and in the User Mapping add the following database roles: db_datareader, db_datawriter and db_ddladmin.

That's what I've always done in the past and works for me, not sure if its all required, but worth a try I guess

Answer 3

Try creating new user TCMDBUser in the database and run the following command

EXEC sp_change_users_login 'Update_One', 'TCMDBUser', 'TCMDBUser'