Problem:

How to configure SQL Server 2005 to use a user different than "sa".

Resolution:

• Product Name: StarTeam
• Product Version: 2008 / 2008 R2
• Product Component: Server / Database
• Platform/OS Version: All Supported OS

Description:

After upgrading to StarTeam 2008 (R2) from 2005 R2 or older, it is required to upgrade from SQL 2000 to SQL 2005. The security profile does not upgrade seamlessly and there is some manual work that must be done to get a non-sa user to work properly with StarTeam server.

Note: This article is not applicable to new configurations created with Server 2008(R2) and SQL 2005.

Answer/Solution:

This issue is caused by the changes made to the SQL Server security model. The backup from SQL 2000 that was imported into SQL 2005 does not properly setup the DB user for use with StarTeam. You will get errors adding views and stored procedures to the database (see There is already an object named "syn_ServerSettings" in the database. ).

To correct this:

1. Create a new database user in SQL Server under Security -> Logins. Do not create a new user under the starteam databases own Security section.

2. Under "User Mapping", check the checkbox under the "Map" column that corresponds to the StarTeam Database. This will allow this user to connect to the StarTeam database.
3. Set the User column to be the name of the user. If your User is called "StarteamSQL", set the column User to be "StarTeamSQL".
4. Set the Default Schema to be the same as well. "StarTeamSQL" following the example name. ***
   
5. Push OK
6. Under the Starteam database"s Security -> Users, select the User"s Properties. Under Role Members, the db_owner checkbox should be selected.
7. The StarTeam server should start at this point, but if you have tried to start it before and got errors, you will need to first drop all the views and stored procedures. DO NOT DROP ANY TABLES. The views and procedures will get recreated the next time you start starteam, and they will belong to the user. (Ex: StarTeamSQL.syn_ServerSettings).
8. If you are not using TeamAnalytics, delete the folder C:\Program Files\Borland\StarTeam Server 2008\DBScripts\Sqlserver_Scripts\DW. If you are using TeamAnalytics, ignore the errors in the server log on startup (see Why does the following error appear in the StarTeam Server log after applying StarTeam 2006 Server Patch 4, "Msg = Invalid object name "dbo.ConvertSTTimestamp"? )
   

*** A new schema will map the user to "dbo", but SQL Server 2005 does not allow you to map a new user to "dbo". Its a limitation in SQL Server and at this time Borland Support does not have a recommendation on how to force the Default Schema to be DBO for a non-sa user.

If the issue persists, please contact Borland Support.

Author: Daniel Rice