InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:18:16 GMT20http://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxthe following should generate the alter schema commands to change schema for any Stored Proc not owned by dbo[code]select 'alter schema dbo transfer ' + sch.name + '.' + tab.namefrom sys.schemas sch inner join sys.objects tab on sch.schema_id = tab.schema_idwhere sch.name <> 'dbo' and tab.type = 'P'order by tab.name [/code]Tue, 10 Mar 2009 12:09:39 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxMoving to 2005Tue, 10 Mar 2009 11:30:44 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxWhy is an SS2K5 question posted in this SS2K forum ?If users keep mixing forum contents, pretty soon it will become that much more difficult to find something in the mass of information.Tue, 10 Mar 2009 11:25:47 GMTJ-440512http://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxhttp://www.developmentnow.com/g/109_2006_5_0_0_758716/change-object-owner-with-a-wildcard.htmMon, 09 Mar 2009 04:11:27 GMTamarnath_skhttp://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxIN SQL Server 2005, typically, objects are not owned by users anymore they are owned by schemas, although a user CAN still own an object, you have to do some work to do it though.I think the query you want to run is:[code]SELECT c.[name] AS schema_name, T.[name] AS table_name, T.[object_id], T.principal_id, T.[schema_id], T.[type], T.type_desc, T.create_date, T.modify_dateFROM sys.tables AS T JOIN sys.schemas AS c ON T.[schema_id] = C.[schema_id][/code]If the principal_id is not null then the object is owned by a user not a schema. Here's a blog post about this: [url]http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/03/04/owning-an-object-in-sql-server-2005-2008.aspx[/url]If you need to change an objects schema, and I don't think this is intuitive at all, you need to use the ALTER SCHEMA command like this (uses AdventureWorks):[code]ALTER SCHEMA Production TRANSFER Purchasing.ProductVendor[/code]Fri, 06 Mar 2009 13:08:41 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxHi,I'm using SQL Server 2005.Sorry.Thanks and regardsJMSM ;)Fri, 06 Mar 2009 11:45:04 GMTJMSMhttp://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxCan you clarify what version of SQL Server you are using? You have posted in a SQL Server 2000 forum, but have used the sys.schemas system view which does not exist in SQL Server 2000.Fri, 06 Mar 2009 10:50:00 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic670471-146-1.aspxHello,We've got a lot of objects in one database that are owned by another user then dbo.I need to know all objects that this owner owns, if i can change them to dbo without any impact in the environment and how can i change all of them to be owned by dbo.I've tried to replicate this environment in a test instance and i use the following query to get the following information, the point is that i can't change the schema/owner of this table and pk to dboselect a.name, a.xtype, a.type, c.name as 'Schema Name'from sysobjects a, sysusers b, sys.schemas cwhere a.name like '%TblOwner%' and b.uid = c.principal_id and c.name like '%Tbl%'goObject Name Xtype Type Schema NameTblOwnerName U U tblownerexamplepk_TblOwnerName PK K tblownerexampleCan you send me the sintax to get all this information?Thanks and regards,JMSM ;)Fri, 06 Mar 2009 10:44:52 GMTJMSM