--**************************************************************************************************
--*		Check for duplicate web projects
--**************************************************************************************************

 
  IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.MSP_WEB_PROJECTS')
  AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

  BEGIN

   IF (SELECT count(*)
    FROM dbo.MSP_WEB_PROJECTS AS F
    WHERE EXISTS
        (SELECT PROJ_ID, substring(PROJ_TIMESTAMP,1,28), PROJ_NAME, WRES_ID, Count(WPROJ_ID)
			   FROM dbo.MSP_WEB_PROJECTS
         WHERE MSP_WEB_PROJECTS.PROJ_ID = F.PROJ_ID
         AND substring(MSP_WEB_PROJECTS.PROJ_TIMESTAMP,1,28) = substring(F.PROJ_TIMESTAMP,1,28)
         AND MSP_WEB_PROJECTS.PROJ_NAME = F.PROJ_NAME
         AND MSP_WEB_PROJECTS.WRES_ID = F.WRES_ID
         GROUP BY MSP_WEB_PROJECTS.PROJ_ID, substring(MSP_WEB_PROJECTS.PROJ_TIMESTAMP,1,28), MSP_WEB_PROJECTS.PROJ_NAME, MSP_WEB_PROJECTS.WRES_ID
         HAVING Count(MSP_WEB_PROJECTS.WPROJ_ID) > 1)) > 0

   BEGIN

     RAISERROR ('The database upgrade script has detected duplicate project entries. Before this database can be upgraded, this problem must be fixed. For more information, please see PSREAD.htm on the installation CD.', 16, 1)
     RETURN

   END

  END

--**************************************************************************************************
--*		MSProjectRole
--**************************************************************************************************

IF NOT EXISTS (SELECT * FROM sysusers WHERE name = N'MSProjectRole')
EXEC sp_addrole N'MSProjectRole'
GO

--**************************************************************************************************
--*		MSProjectServerRole
--**************************************************************************************************

IF NOT EXISTS (SELECT uid FROM sysusers WHERE name = N'MSProjectServerRole') 
EXEC sp_addrole N'MSProjectServerRole', N'dbo'
GO

sp_addrolemember N'db_datareader', N'MSProjectServerRole'
GO

sp_addrolemember N'db_datawriter', N'MSProjectServerRole'
GO
