SQL SERVER – 2005 – Transferring Ownership of a Schema to a User

One of the blog reader asked me how transfer of ownership of schema to another users. Follow the simple script and you will be able to transfer ownership of schema to another user.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO UserName;
GO

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts
Previous Post
SQL Server – Good Articles on Database Collation
Next Post
SQL SERVER – 15 Best Practices for Better Database Performance

Related Posts

7 Comments. Leave new

  • Hi,

    Sorry to say that i followed each of difrent step u sugest for delete duplicate data but its not working in my data base.

    1 2 12-Apr-2008 9:00 AM 11:00 PM Saturday 14 4/12/2008 3:17:11 AM 04 Saturday 2008
    2 2 12-Apr-2008 7:00 AM 11:00 PM Saturday 16 4/12/2008 3:19:27 AM 04 Saturday 2008
    3 2 11-Apr-2008 9:00 AM 10:00 PM Friday 13 4/11/2008 3:24:32 AM 04 Friday 2008
    7 2 11-Apr-2008 8:00 AM 10:00 PM Friday 14 4/11/2008 3:28:18 AM 04 Friday 2008
    8 2 11-Apr-2008 5:00 AM 10:00 PM Friday 17 4/11/2008 3:29:20 AM 04 Friday 2008
    9 2 11-Apr-2008 10:30AM 11:30PM FriDay 12 fbggfb 4/12/2008 3:17:11 AM dfbdfbdfb 04 FriDay 2008

    Reply
  • I am faced with this as well. Thank you! I am a newbie so bare with me. I want to see all ownership of schema’s and groups or databases for a particular user account. What would be the easiest way to accomplish this?

    Reply
  • @ Dhanu

    Can you please discuss in detail .. what exactly you are trying to do …

    @ Sam

    In object explorer if you click on schema ( Just slingle click as if you are selecting it and on your right hand side in summary section you will see all the schemas and their owner.

    To my knowledge there can be only one owner ( database role or database user) for one schema.

    If you click user properties ( object explorer, server- database – security- users – double click on the specific user ) you will be able to see what all schemas he owns and what all permission he has, if you click securables in user properties … you will be able to see what all permission the user or database role has on database objects.

    Reply
  • how to work with windowsAuthorization in one computer

    Reply
  • Hi Pinal,

    I have give owned schema db_datareader permission to a user and thru SSMS and now i want to change the db_datawriter ,but i was unable to change because db_datareader was disabled which i was given earlier,

    how to enable this option,please let me know…

    Thanks
    Koteswar Rao

    Reply
  • Thanks, Pinal. Exactly what I need to change the schema owner to allow the previous owner to be dropped.

    Reply
  • Another score for you. Thanks!

    Reply

Leave a Reply