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

Mass Change Database Owner

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
msiedle
Asked:
msiedle
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
msiedleAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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