Geeks With Blogs
Sharvan Dhaka blog
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 in the database.

EXEC sp_changeobjectowner @oldownerplusobject, @new

create procedure _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 _ChangeObjectOwner 'p','xxx','yyy'
Posted on Sunday, October 15, 2006 5:54 PM MS Sql , Tricky Solution/ Code | 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 © Sharvan Dhaka | Powered by: GeeksWithBlogs.net