[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mass Change Database Owner

Posted on 2006-04-11
2
Medium Priority
?
517 Views
Last Modified: 2010-05-18
Hi all

I need to change all tables in my database that are owned by user1 to user2, and I know how to do this table by table, but is there a way to make this a little more automated so that it automatically iterates through all my tables and sets the owner to 'user2'? ...or am I stuck writing the following for each table?

sp_changeobjectowner
 'user1.eHR_Accred_Communication', 'user2'

Cheers,
Mark
0
Comment
Question by:msiedle
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 16432874
Hi msiedle,
DECLARE @dbObject varchar(200)
DECLARE CurDbObjects CURSOR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME = 'User2'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE= 'U'ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''user1''')
FETCH NEXT FROM CurDbObjects INTO @dbObject
END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects

Aneesh
0
 
LVL 2

Author Comment

by:msiedle
ID: 16432908
works great ...

had to mod the user's around, but other than that perfect ...

DECLARE @dbObject varchar(200)
DECLARE CurDbObjects CURSOR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME = 'user1'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE= 'U'ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''user2''')
FETCH NEXT FROM CurDbObjects INTO @dbObject
END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects

thanks :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question