Change Object Owner - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Change Object Owner

Author

Topic

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 02:34:18

Hi,

Can anybdy please assist me to change owner of my database objects?

Currently all my database object owner is OldAdmin. Therefor all my db object appear as

OldAdmin.Table1
OldAdmin.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

Don't sit back because of failure. It will come back to check if you still available. -- Binu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 02:43:00

Read about sp_changeobjectowner in SQL Server help.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 03:03:04

Thanks Harsh,

I am sorry I forgot to mention that my database is in SQL 2005.

I checked the books online for sp_changeobjectowner and it says..

"Important:
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.
"

I tried using Alter Schema... It works for user DBO

ALTER SCHEMA dbo TRANSFER [OldAdmin].[ObjectName]

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.

Any help will be appreciated.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 03:21:00

Make sure Schema 'NewAdmin' is present in your current database.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2007-07-06 : 03:28:44

Yes its there. But still getting the error :(.

Please help.

Thanks
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-06 : 11:12:42

Who is owner of that schema?

DaleJ
Starting Member

7 Posts

Posted - 2007-07-06 : 18:16:33

Have you tried ALTERing to reassign the AUTHORIZATION?

ALTER AUTHORIZATION ON SCHEMA::OldAdmin TO NewAdmin;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources