How to change object owner from ”dbo” to ”td” in Quality Center project database

  • Questions
  • How to change object owner from ''dbo'' to ''td'' in Quality Center project database
Question ID: 107498
0
0

How to change object owner from "dbo" to "td" in Quality Center project database?

Marked as spam
Posted by (Questions: 122, Answers: 3)
Asked on March 10, 2017 7:34 pm
17 views
Answers (1)
0
Private answer

Quality Center (QC) project databases will sometimes have multiple owners. It might be necessary to change the owner table name to ''td'' when using sql authentication in Microsoft Sql server (or another name if required). This can be done with sp_changeobjectowner. Also this store procedure can be use to change the owner name to ''dbo'' when tables are owned by ''td'' and customer wants to use windows authentication.

1. Make sure the project database has 'td' as user and the td schema owner is td
2. Run the following store procedure in each table:

- sp_changeobjectowner 'CurrentOwner.TableName','expectedOwner';
- Example: sp_changeobjectowner 'dbo.CROS_REF','td';

3. Run SP to change DB ownwership:

- EXEC sp_change_users_login 'Report'

EXEC sp_change_users_login 'Update_One', 'td', 'td';

Alternatively issue the following query to generate statements for all databases in the SQL

SELECT
'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
+ 'EXEC sp_change_users_login ' + CHAR(39) + 'Report' + CHAR(39) + CHAR(13) + CHAR(10)
+ 'EXEC sp_change_users_login ' + CHAR(39) + 'Update_One' + CHAR(39) + ', ' + CHAR(39) + 'td'+ CHAR(39) + ', ' + + CHAR(39) + 'td'+ CHAR(39) + CHAR(13) + CHAR(10)
+ 'GO'
+ CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM sys.databases d where name not in ('master','tempdb','model','msdb') or name not like '%report%'

Copy the results from the above query and paste into a new query and issue.

Marked as spam
Posted by (Questions: 3, Answers: 168)
Answered on March 10, 2017 7:39 pm