Fixing the Owner of DB Objects
Depending on the way you have your SQL database security set up you may end up with stored procedures and other objects having an owner other than dbo. The typical case of this happening is when running the upgrade with an account that is not an SA on the SQL Server.
If unresolved, this naming issue can give you grief. Here's a way to fix it.
This ownership issue will ultimately end up in causing a lot of errors such as the Deployment Console not opening or certain tasks failing. Basically anything that tries to call the stored procedure will fail because the name is wrong. For example: A stored procedure in the DS database called dbo.del_computer may have its name changed during an upgrade to username.del_computer.
To fix this situation run the following SQL script. Change the USERNAME to whatever the owner is for the objects that need to be changed.
DECLARE @OldOwner sysname, @NewOwner sysname SET @OldOwner = 'USERNAME' SET @NewOwner = 'dbo' DECLARE CURS CURSOR FOR SELECT name FROM sysobjects WHERE type = 'p' AND uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) AND NOT name LIKE 'dt%' FOR READ ONLY DECLARE @ProcName sysname OPEN CURS FETCH CURS INTO @ProcName WHILE @@FETCH_STATUS = 0 BEGIN IF @@VERSION >= 'Microsoft SQL Server 2005' BEGIN EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName) exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner') END ELSE EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''') FETCH CURS INTO @ProcName END CLOSE CURS DEALLOCATE CURS