SQL Tips by Namwar Rizvi

June 24, 2007

Changing the Object Schema in SQL Server 2005

Filed under: Information,New Features,Query,SQL Server 2005,tips,TSQL — namwar @ 9:19 PM

In SQL Server 2000 there was no concept of SCHEMA. The ownership of the given object was maintained directly through user. Changing of the owenership could be performed by using the system stored procedure called sp_changeobjectowner.

Now, in SQL Server 2005 scenario has been changed. Objects are no longer directly linked to the user; instead they are linked to the SCHEMA. Any user who has the proper rights to the required SCHEMA can access the objects of it.

So now the question is what is the replacement of sp_changeobjectowner in SQL Server 2005? It is ALTER SCHEMA command.
You can change the schema of an object by using ALTER SCHEMA command. This command takes two arguments:

  1. Target Schema name you want to transfer the object to
  2. Object Name which you to transfer

Following is an example of ALTER SCHEMA command

ALTER SCHEMA
siteadmin —Target Schema
TRANSFER
dbo.testTable –Object to be transferred

Please ensure that you have scripted all the permission of original object before transferring to new schema because permissions granted to original object e.g. dbo.testTable will be removed during transfer.

Advertisements

6 Comments »

  1. Lush yaar Namwar bhai ye change object owner walee baat mujhey nahi pata thee šŸ™‚ sql 2005 tou cheeta ho gaya hai šŸ™‚

    Comment by Waliullah Kashan — June 26, 2007 @ 11:20 AM | Reply

  2. This post was very use full for me. thank you very much

    Comment by Shahana — September 11, 2008 @ 5:41 AM | Reply

  3. Alhamdulillah

    thanks for the tips. very helpful.

    Comment by gelay — October 1, 2009 @ 10:17 AM | Reply

  4. Thanks, it helped me..

    Comment by Asharaf Ali — June 11, 2010 @ 7:41 AM | Reply

  5. Thanks…

    Comment by Naresh Vidhani — June 3, 2011 @ 7:14 AM | Reply

  6. Thank’s

    Comment by TSDP — September 12, 2012 @ 3:39 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: