How to change owner permissions from ________ to dbo

I just switched SQL servers and my old stored procedures read dbo.whatever, etc. whereas some of my new tables aren't dbo they're mattbrigh

How do I change those to dbo or do I need to change my stored procedures to mattbrigh from dbo?
mattybrighAsked:
Who is Participating?
 
blobblesCommented:
Run this, it will change all your tables and procedures to dbo:

--*********************************
DECLARE @Name varchar(1000)
DECLARE @UID int

-- Declare a cursor and setup
DECLARE NameUpdate CURSOR LOCAL FAST_FORWARD
FOR
SELECT [Name], uid FROM sysobjects
WHERE Type <> 'S'
AND (xType = 'P' OR xType = 'U')
AND uid <> 1

-- Open the cursor
OPEN NameUpdate

-- Fetch the Data into the variables
FETCH NEXT FROM NameUpdate
INTO @Name,@UID

-- Loop through the duplicates setting the next duplicates deletedatetime
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @Name = (SELECT name FROM sysusers WHERE uid = @UID) + '.' + @Name

      EXEC sp_changeobjectowner @Name, 'dbo'
      
      FETCH NEXT FROM NameUpdate
      INTO @Name,@UID

END

CLOSE NameUpdate
DEALLOCATE NameUpdate
--*****************************************************

0
 
Scott PletcherSenior DBACommented:
You'd be much better off changing the tables.

Use: EXEC sp_changeObjectOwner -- see Books Online for more details, or post a follow-up comment here :-) .
0
 
Scott PletcherSenior DBACommented:
For example, to generate the commands:

SELECT 'EXEC sp_changeObjectOwner ''' + name + ''', ''dbo'''
FROM sysobjects
WHERE xtype = 'U'
AND USER_NAME(uid) <> 'dbo'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mattybrighAuthor Commented:
And where would I enter that info.?  Query analyzer?
0
 
SjoerdVerweijCommented:
Yep.
0
 
mattybrighAuthor Commented:
Is "+ name +" the table name?
0
 
arbertCommented:
Just had this same question earlier today for views--look at the above post and change the 'VIEW' in the query to 'TABLE'


http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21116914.html
0
 
mattybrighAuthor Commented:
No dice.  It runs in Query Analyzer and says it works, but when I look at my tables in Ent. Manager, the type is still "User" and the owner is still "mattybrigh"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.