February 15, 2007 at 9:47 am
In every database in SQL Server 2000 there is a dbo.sysobjects table with UID column. DBO UID is 1, so whatever is not 1 is not owned by DBO.
In 2005 sys.sysobjects is a view for the backward compatibility, but works anyway.
If I login as a sysadmin and run a query without qualifiers, it works:
select
* from sysobjects where UID <>1
I would never recommend changing ownership unless something does not work. A great number of third-party applications make object owners their specific login for the application to work. Also it is very important for security. Please, read about Ownership Chain before doing anything at all.
Regards,Yelena Varsha
February 15, 2007 at 10:43 am
"I also tried using sp_MSforeachdb to no avail"
You need to be aware of the parameter @replacechar, which defaults to '?'. Try this SQL:
if object_id('tempdb..#Objects') is not null drop table #Objects
go
create table #Objects
(DatabaseNamesysname
,ObjectNamesysname
,ObjectOwnersysname
,ObjectTypevarchar(26)
)
exec sp_msforeachdb
@replacechar = '?'
, @command1 = '
insert into #Objects
(DatabaseName,ObjectName ,ObjectOwner,ObjectType)
select ''?''
,o.name
,user_name(uid)
,substring(v.name,5,31)
from [?].dbo.sysobjects o, master.dbo.spt_values v
where o.xtype = substring(v.name,1,2) collate database_default
anduser_name(uid) ''dbo''
and v.type = ''O9T''
'
select * from #objects
SQL = Scarcely Qualifies as a Language
February 16, 2007 at 8:52 am
I tweaked your cursor method a bit:
--LOOP THROUGH ALL DATABASES ON SERVER AND DISPLAY ALL OBJECTS/OWNERS--
DECLARE @name sysname
, @sql varchar(8000)
DECLARE dbname CURSOR FOR
SELECT DISTINCT name FROM master.dbo.sysdatabases
OPEN dbname
FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'USE ' + @name + '
select DB_Name() AS DatabaseName,
''Name'' = o.name,
''Owner'' = user_name(uid),
''Object_type'' = substring(v.name,5,31)
from sysobjects o, master.dbo.spt_values v
where o.xtype = substring(v.name,1,2) collate database_default and v.type = ''O9T''
order by Owner asc'
EXEC(@sql)
-- PRINT @sql
FETCH NEXT FROM dbname INTO @name
END
CLOSE dbname
DEALLOCATE dbname
February 22, 2007 at 11:59 am
I just wanted to say thanks to all those who took the time to respond and help me with this query.
It is much appreciated!
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply