March 6, 2009 at 10:44 am
Hello,
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 dbo
select a.name, a.xtype, a.type, c.name as 'Schema Name'
from sysobjects a, sysusers b, sys.schemas c
where a.name like '%TblOwner%' and b.uid = c.principal_id and c.name like '%Tbl%'
go
Object NameXtypeTypeSchema Name
TblOwnerNameU U tblownerexample
pk_TblOwnerNamePKK tblownerexample
Can you send me the sintax to get all this information?
Thanks and regards,
JMSM 😉
March 6, 2009 at 10:50 am
Can 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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 6, 2009 at 11:45 am
Hi,
I'm using SQL Server 2005.
Sorry.
Thanks and regards
JMSM 😉
March 6, 2009 at 1:08 pm
IN 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:
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_date
FROM
sys.tables AS T JOIN
sys.schemas AS c
ON T.[schema_id] = C.[schema_id]
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: http://www.sqlservercentral.com/blogs/brian_kelley/archive/2009/03/04/owning-an-object-in-sql-server-2005-2008.aspx
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):
ALTER SCHEMA Production TRANSFER Purchasing.ProductVendor
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2009 at 4:11 am
March 10, 2009 at 11:25 am
Why 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.
March 10, 2009 at 11:30 am
Moving to 2005
March 10, 2009 at 12:09 pm
the following should generate the alter schema commands to change schema for any Stored Proc not owned by dbo
select 'alter schema dbo transfer ' + sch.name + '.' + tab.name
from sys.schemas sch inner join sys.objects tab on sch.schema_id = tab.schema_id
where sch.name <> 'dbo' and tab.type = 'P'
order by tab.name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply