Welcome to weblogs.com.pk Sign in | Join | Help

Attaching/Detaching SQL database :: sp_ChangeObjectOwner

If you detach your database and attach it to another SQL Server instance; chances are that same logins doesn’t exists. If there are some database objects in a database which are not owned by dbo but by some login which might not exists in the second server, you should change owner of such objects to dbo

sp_changeobjectowner @objname = ‘SomeTableOrView’, @newowner = ‘dbo’

And if there are too many objects; try this query

use yourdatabase
select 'exec sp_changeobjectowner @objname = ''' + ltrim(u.name) + '.[' + ltrim(s.name) + ']'', @newowner = ''dbo'';'
from sysobjects s join sysusers u on s.uid = u.uid
where s.xtype in ('V', 'P', 'U')
 and u.name <> 'dbo'

order by s.name

Copy/paste its output and execute it.

If you are moving your database to SQL 2005 by detaching/attaching; I strongly recommend to do the above before doing this.


Published Monday, January 2, 2006 4:02 PM by khurram
Filed under: ,


No Comments

New Comments to this post are disabled