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.
Answer by PT Barnum · Jan 14, 2014 at 06:40 PM
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).
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'
To go the other way, switch Ownership from td (SQL-Auth) to DBO (Win-auth): run this SQL:
sp_MSforeachtable @command1="sp_changeobjectowner '?', 'dbo'"