Geeks With Blogs
Gaurav Taneja Great dreams... never even get out of the box. It takes an uncommon amount of guts to put your dreams on the line, to hold them up and say, "How good or how bad am I?" That's where courage comes in.

To do this, execute the following command on every table

exec sp_changeobjectowner '?????', 'DBO' - where ????? is the name of your database table.

Also below is the stored procedure through which you can change the owner for every table inthe database.

EXEC sp_changeobjectowner @oldownerplusobject, @new

create procedure J_ChangeObjectOwner (@type varchar(1),@old varchar(20),@new

varchar(20))

as

declare @ObjectName varchar(100)

declare @oldownerplusobject varchar(50) begin declare Cursor_Object cursor for select [name] from sysobjects where type=@type and xtype=@type open Cursor_Object FETCH NEXT FROM Cursor_Object INTO @ObjectName WHILE @@FETCH_STATUS = 0 begin

set @oldownerplusobject=@old+'.'+@ObjectName

EXEC sp_changeobjectowner @oldownerplusobject, @new

print 'Permission Changed for ' + @oldownerplusobject +' to ' + @new + ' :

Process Done'

FETCH NEXT FROM Cursor_Object INTO @ObjectName end close Cursor_Object deallocate Cursor_Object end

exec J_ChangeObjectOwner 'p','xxx','yyy'


 

Also below is the procedure you can first create and then execute


if exists (select * from sysobjects where id = object_id(N'[dbo].[chObjOwner]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[chObjOwner]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

CREATE proc chObjOwner( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName is the current user
-- @newUsrName is the new user

set nocount on
declare @uid int                   -- UID of the user
declare @objName varchar(50)       -- Object name owned by user
declare @currObjName varchar(50)   -- Checks for existing object owned by new user
declare @outStr varchar(256)       -- SQL command with 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects where uid = @uid

open chObjOwnerCur
if @@cursor_rows = 0
begin
  print 'Error: No objects owned by ' + @usrName
  close chObjOwnerCur
  deallocate chObjOwnerCur
  return 1
end

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + "." + @objName
  if (object_id(@currObjName) > 0)
    print 'WARNING *** ' + @currObjName + ' already exists ***'
  set @outStr = "sp_changeobjectowner '" + @usrName + "." + @objName + "','" + @newUsrName + "'"
  print @outStr
  print 'go'
  fetch next from chObjOwnerCur into @objName
end

close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0


GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON
GO

then execute
exec chObjOwner 'test','dbo'

the result provided can be copy pasted and then executed
Posted on Friday, August 4, 2006 4:30 AM SQL SERVER | Back to top


Comments on this post: How to change the ower of tables in Microsoft SQL Server database

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Gaurav Taneja | Powered by: GeeksWithBlogs.net