SQL Server Performance Forum – Threads Archive
Change Object Owner
Hi, Can anybdy please assist me to change owner of my database objects? My database is in SQL server 2005 with SP2. Currently all my database object owner is OldAdmin. Therefor all my db object appear as OldAdmin.Table1OldAdmin.Table2
OldAdmin.Table3
.
.
Now I want to change the object owner to NewAdmin so that they should appear as NewAdmin.Table1
NewAdmin.Table2
NewAdmin.Table3
.
.
. Please help. Regards
Sachin
[You could take help of Sp_changeobjectowner and BOL quotes
quote:
This stored procedure only works with the objects available in Microsoft SQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names
This stored procedure only works with the objects available in Microsoft SQL Server 2000. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SCHEMA or ALTER AUTHORIZATION instead. sp_changeobjectowner changes both the schema and the owner. To preserve compatibility with earlier versions of SQL Server, this stored procedure will only change object owners when both the current owner and the new owner own schemas that have the same name as their database user names
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I tried using Alter Schema… It works for user DBO ALTER SCHEMA dbo TRANSFER [OldAdmin].[ObjectName] — Works! But when I try to change the owner other than DBO like Newadmin ALTER SCHEMA NewAdmin TRANSFER [OldAdmin].[ObjectName] I get the below error. Cannot alter the schema ‘NewAdmin’, because it does not exist or you do not have permission. Newadmin already exists in the database and have db_owner rights. Any help will be appreciated. Thanks
Sachin
http://www.sqlskills.com/blogs/bobb/2006/01/30/AboutSCHEMAsAndSettingUpQueryNotifications.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi Satya, Thanks a lot. It worked for me. Regards
Sachin
]]>