February 15, 2007 at 9:35 am
I have an issue that I need to resolve.
We recently had one of our SQL Server DBAs leave the company and now I am tasked with trying to locate any objects that he may have created (databases, tables, stored procedures, views, etc.) during his time here.
I need to write a T-SQL query that I can run on each of our servers (I’ll log on each server individually- mostly SQL Server 2000 with a few SQL Server 2005) that goes through all the databases on the server and returns any object not owned by dbo.
The long term goal being once all the objects have been identified would be to change ownership to the sa account.
I am a fairly new DBA so please bear with me.
I have the following code but it doesn’t loop through all the databases as expected (it just loops through the same database over and over). I also tried using sp_MSforeachdb to no avail.
It also returns the dbo account which I would rather not see in order to keep the output minimal:
--TO DETERMINE ALL DATABASE OWNERS--
sp_helpdb
--LOOP THROUGH ALL DATABASES ON SERVER AND DISPLAY ALL OBJECTS/OWNERS--
declare @name sysname
declare dbname cursor for
select distinct name from master.dbo.sysdatabases
OPEN dbname
FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
select
'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
FETCH NEXT FROM dbname INTO @name
END
CLOSE dbname
DEALLOCATE dbname
Any ideas on how to get the above to correctly return ownership information (excluding the dbo account if possible) for each database on the server?
Any help would be greatly appreciated!
Thanks.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply