Video Screencast Help
Endpoint Management Community Blog

Fixing the Owner of DB Objects

Created: 21 Dec 2007 • Updated: 21 Dec 2007 • 1 comment
robertser's picture
0 0 Votes
Login to vote

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

Comments 1 CommentJump to latest comment

Marc De Schepper's picture

All very true, execpt you also need to run these for type F and V...and for each user you see in the database user1.del_computer and user2.del_computer.

+3
Login to vote