Reply to thread

Message: [QUOTE="Wharton Computer Consulting, post: 4980382"] FYI: While testing the migration of ProjectServer databases from SQL 2000 to SQL 2005, it was found at times the MSProjectServerUser and MSProjectUser did not come across cleanly. The SQL migration was done this way. 1) Set up new cluster hardware on 64 bit computers 2) Installation of 2 node SQL cluster on 64 bit 3) Copy user accounts from SQL 2000 to SQL2005 4) Deattach SQL2000 database, copy to SQL 2005 server and then ATTACH database Sometimes the SQL authenticated accounts came over fine. Other times the came across but we had to reset the password and evern stranger, we had to delete and recreate accounts. All the NT accounts worked fine. The MSProjectServerUser and MSProjectUser account needed to be delete and recreated, but this wrecks the security model. Below is a script I wrote to fix this. -- MWHARTON -- 12/19/2007 -- If you read this have a Merry Christmas -- -- Purpose: -- Is to delete and recreate the MSProjectUser and MSProjectServerUser accounts -- They cannot be delete, because they are owners of schemas and roles -- So the process is to create MSProjectUserX and MSProjectServerUserX accounts -- and reassign roles and schemmas to these accounts -- Then delete the MSProjectUser and MSProjectServerUser accounts -- and go thru process again by reassigning back to the MSProjectUser and MSProjectServer User account -- -- Create users in Security folder for SQL Server: USE MASTER CREATE LOGIN MSProjectServerUser WITH PASSWORD = 'project1', CHECK_POLICY=OFF CREATE LOGIN MSProjectUser WITH PASSWORD = 'project1', CHECK_POLICY=OFF USE ProjectServer CREATE USER MSProjectServerUserX FOR LOGIN MSProjectServerUserX WITH DEFAULT_SCHEMA = MSProjectServerUser -- Note the roles in SQL 2000 are public, db_owner, MSProjectServerRole EXEC sp_addrolemember 'db_datareader', 'MSProjectServerUserX' EXEC sp_addrolemember 'db_datawriter', 'MSProjectServerUserX' EXEC sp_addrolemember 'db_owner', 'MSProjectServerUserX' EXEC sp_addrolemember 'MSProjectServerRole', 'MSProjectServerUserX' CREATE USER MSProjectUserX FOR LOGIN MSProjectUserX WITH DEFAULT_SCHEMA = MSProjectUser -- Note the roles in SQL 2000 are public, db_reader, MSProjectRole EXEC sp_addrolemember 'db_datareader', 'MSProjectUserX' EXEC sp_addrolemember 'MSProjectRole', 'MSProjectUserX' -- -- Change Ownership of Project Schemas -- CREATE SCHEMA [MSProjectServerRole] AUTHORIZATION [MSProjectServerRole] ALTER AUTHORIZATION ON SCHEMA::MSProjectServerRole TO MSProjectServerUserX ALTER AUTHORIZATION ON SCHEMA::MSProjectRole TO MSProjectUserX -- ALTER AUTHORIZATION ON SCHEMA::MSProjectServerUser TO MSProjectServerUserX ALTER AUTHORIZATION ON SCHEMA::MSProjectUser TO MSProjectUserX -- Delete Users DROP User MSProjectServerUser DROP User MSProjectUser -- -- -- -- -- -- -- Now we go thru the process of reversing what we just done -- --USE MASTER --CREATE LOGIN MSProjectServerUserX WITH PASSWORD = 'project1', CHECK_POLICY=OFF --CREATE LOGIN MSProjectUserX WITH PASSWORD = 'project1', CHECK_POLICY=OFF USE ProjectServer CREATE USER MSProjectServerUser FOR LOGIN MSProjectServerUser WITH DEFAULT_SCHEMA = MSProjectServerUser -- Note the roles in SQL 2000 are public, db_owner, MSProjectServerRole EXEC sp_addrolemember 'db_datareader', 'MSProjectServerUser' EXEC sp_addrolemember 'db_datawriter', 'MSProjectServerUser' EXEC sp_addrolemember 'db_owner', 'MSProjectServerUser' EXEC sp_addrolemember 'MSProjectServerRole', 'MSProjectServerUser' CREATE USER MSProjectUser FOR LOGIN MSProjectUser WITH DEFAULT_SCHEMA = MSProjectUser -- Note the roles in SQL 2000 are public, db_reader, MSProjectRole EXEC sp_addrolemember 'db_datareader', 'MSProjectUser' EXEC sp_addrolemember 'MSProjectRole', 'MSProjectUser' -- -- Change Ownership of Project Schemas -- CREATE SCHEMA [MSProjectServerRole] AUTHORIZATION [MSProjectServerRole] -- Role schema ALTER AUTHORIZATION ON SCHEMA::MSProjectServerRole TO MSProjectServerUser ALTER AUTHORIZATION ON SCHEMA::MSProjectRole TO MSProjectUser -- User Schema ALTER AUTHORIZATION ON SCHEMA::MSProjectServerUser TO MSProjectServerUser ALTER AUTHORIZATION ON SCHEMA::MSProjectUser TO MSProjectUser -- Delete Temp Users DROP User MSProjectServerUserX DROP User MSProjectUserX = [/QUOTE]

Verification