October 8, 2008 at 4:40 pm
How do I find the objects owned by a specific login. If found how can it be changed to dbo? on a SQL 2000 Sp4 system.
Thanks in Advance!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
October 8, 2008 at 4:51 pm
To find objects owned by specific logins use:
select user_name(uid) as OwnerName,so.name as ObjectName from dbo.sysobjects so where uid<>user_id('dbo')
And to change the owner back to dbo or any other user of your choice use:
@DBname--Input parameter for database which you want to query
@Newname--UserName to which you want to make the owner of object
--Exec testsp 'Northwind','dbo'--Will make dbo as the owner of all objects owned by user other than dbo.
Create procedure testsp (@DBname sysname, @Newname sysname)
As
DECLARE @ObjectName SYSNAME
DECLARE @UserName SYSNAME
DECLARE @login SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE @SQL1 NVARCHAR(300)
Create table #temp (UserName SYSNAME, ObjectName SYSNAME)
Set @login=@Newname
Set @SQL1='select user_name(uid),so.name from '+@DBname+'.dbo.sysobjects
so where uid<>user_id(''dbo'')'
Print @SQL1
Insert into #temp
Exec (@SQL1)
DECLARE cur_Users CURSOR FOR
Select * from #temp
OPEN cur_Users
FETCH NEXT
FROM cur_Users INTO @UserName,@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'EXEC '+@DBName+'.dbo.'+'sp_changeobjectowner ' + '''' + @UserName+'.'+@ObjectName + '''' + ', ' + '''' + @login + ''''
EXEC sp_executesql @SQL
FETCH NEXT
FROM cur_Users INTO @UserName,@ObjectName
END
CLOSE cur_Users
DEALLOCATE cur_Users
Test it in test environment before running in production.
HTH
MJ
October 8, 2008 at 4:57 pm
Thats awesome many..thanks a lot
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply