Is there an easy way to switch our SQL-Server QC Project DB’s from Windows-auth to SQL-auth?

  • Questions
  • Is there an easy way to switch our SQL-Server QC Project DB's from Windows-auth to SQL-auth?
Question ID: 105205

We are upgrading our QC server to ALM11.52 and our SQL-Server to SQL2008. We are also migrating all of our Databases from a SQL-Server 2005 instance that was using "Windows authentication" to a new SQL-Server 2008 insance the we want to use "SQL-authentication" with.

Is there an easy way to switch this? When we look at our tables for our old Project Databases, each one is prefixed with "dbo." — I guess that means "Windows authentication"?

On our new server (we did a NEW creation of the qcsiteadmin_db with SQL-authentication), we created a new project and looking at the tables for that database, each table is prefixed with "td.". This must mean it is using "SQL Authentication", since we now have a user, "td" in SQL Server that was created by QC when we installed it.

Marked as spam
Posted by (Questions: 185, Answers: 13)
Asked on January 14, 2014 6:21 pm
Answers (1)
Private answer

Your assumptions are correct about the ''dbo.'' and ''td.''. They imply the ''ownership'' of the DB and tables in it and are in fact a clue to whether the QC/ALM project was setup to use ''Windows-authentication'' or ''SQL-authentication''.

Make sure first that you have a backup of your project DB(s).

DBO -> td - DBO (Win-auth) to td (SQL-Auth)
You can switch from one to the other by running this simple SQL command (stored procedure):

To switch Ownership from DBO (Win-auth) to td (SQL-Auth),
Run this SQL from a query window in SQL-Server Management Studio (focused on your project DB):

***sp_MSforeachtable @command1=''sp_changeobjectowner '?', 'td'''***

[this command will cycle through all tables in the project]

Then follow with these 2 commands to ''fix'' the TD user (tie the td user in the DB to the one global to SQL-Server):

***EXEC sp_change_users_login 'Report'***

***EXEC sp_change_users_login 'Update_One', 'td', 'td'***

td -> DBO - td (SQL-Auth) to DBO (Win-auth)


To go the other way, switch Ownership from td (SQL-Auth) to DBO (Win-auth):
run this SQL:

***sp_MSforeachtable @command1=''sp_changeobjectowner '?', 'dbo'''***

Marked as spam
Posted by (Questions: 3, Answers: 466)
Answered on January 14, 2014 6:40 pm
Hi @PT Barnum, As per your script we can change db tables from dbo to td but how can we change '' VIEWS'' from dbo to td . I am getting ''Schema differences were found '' error in upgrade process. Could you help me on this . Thank you, Praveen
( at October 13, 2016 9:22 am)