I am trying to change owner of all tables in a database. How can I do
it?
it?
-
-
Re: How to change owner for all tables in a database.
(alexkuzn@gmail .com) writes:DECLARE @tbl sysnameI am trying to change owner of all tables in a database. How can I do
it?
DECLARE tblcur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype = 'U' AND uid = user_id('oldown er')
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @tbl
IF @@fetch_status <0
BREAK
EXEC sp_changeobject onwer @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, esquel@sommarsk og.se
Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at
Comment