471,591 Members | 2,015 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,591 software developers and data experts.

How to change owner for all tables in a database.

I am trying to change owner of all tables in a database. How can I do
it?

Aug 3 '06 #1
1 17300
(al******@gmail.com) writes:
I am trying to change owner of all tables in a database. How can I do
it?
DECLARE @tbl sysname
DECLARE tblcur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype = 'U' AND uid = user_id('oldowner')
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @tbl
IF @@fetch_status <0
BREAK

EXEC sp_changeobjectonwer @tbl, 'oldowner', 'newowner'
END
DEALLOCATE tblcur

The above is untested, and you may have to look up details in Books Online.

Furthermore, I'm assuming SQL 2000. On SQL 2005, the preferred solution is
different. In fact, odds are good that on SQL 2005 you would not need to do
this at all, since schema and onwer are separated. All depending on why you
want to change the owner, that is.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Victor Spång Arthursson | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.