• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

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?
0
mattybrigh
Asked:
mattybrigh
1 Solution
 
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
 
mattybrighAuthor Commented:
And where would I enter that info.?  Query analyzer?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now