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.


Reference : Pinal Dave (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – 2005 – Transferring Ownership of a Schema to a User

  1. 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


  2. 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?


  3. @ 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.


  4. 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…

    Koteswar Rao


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s