We have an old installation of SharePoint 2007 that functions as a stand-alone server. Recently, it has been running out of space so I decided to move the database to a separate server. My approach was to do detach the database from the old server, copy it to the new server, and re-attach it.
I found that all the service accounts on the old SharePoint server was using ‘Network Service’. This resulted in the following error when I tried to re-attach the content database.
SQL Database ‘SP_CONTENT’ on SQL Server instance ‘<new dbserver>’ not found. Additional error information from SQL Server is included below.
Cannot open database “SP_CONTENT” requested by the login. The login failed.
Login failed for user ‘domain\<servername>$’.
One approach was to use the following SQL command to give domain\<servername>$ access to the database in question:
CREATE LOGIN [domain\server$] FROM WINDOWS;
This would work but would also cause a security risk, since anyone with access to our old SharePoint server could easily break into the database server.
The next approach was to change the service accounts from ‘Network Service’ to domain accounts and assign the required permissions on the database server. I used the following command to do so:
stsadm -o updatefarmcredentials -userlogin DomainName\UserName -password NewPassword
This command updates the ‘Windows SharePoint Services Timer’ service account and the Central Administration IIS pool account.
I then reconfigured all our web applications to use domain accounts using Central Administration -> Operations -> Service Accounts
After performing the above steps, the database attach work perfectly and my site collection was up and running without any problems.