Is there an easy way to switch our SQL-Server QC Project DB’s from Windows-auth to SQL-auth?
| ♥ 0 |
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 |
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) To switch Ownership from DBO (Win-auth) to td (SQL-Auth), ***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): ***sp_MSforeachtable @command1=''sp_changeobjectowner '?', 'dbo'''*** Marked as spam
 Posted by pt-barnum (Questions: 4, Answers: 509) Answered on January 14, 2014 6:40 pm
|