SQL Authentication may fail when migrating from SQL 2000 to SQL 20

[Wharton Computer Consulting]

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
=