question

FreeBird Rajesh avatar image
FreeBird Rajesh asked

Change Table schema

How to change the table schema? I have created one table with different user id . so its created a table called raj.studentMaster, but not able access that table from other login, In my application i used different credentilas, so i need to change that to dbo.StudentsMaster. How to change it?

sql-server-2005sqlschema
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Use the ALTER SCHEMA statement

ALTER SCHEMA dbo TRANSFER raj.studentMaster
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As always, an excellent answer. If I am reading the question right though part of the problem may be with permissions, so you may need to change the permissions as well as the schema to completely solve it, depending on how the permissions are assigned in that organization.
0 Likes 0 ·
Actually, I should have also mentioned how to create objects to avoid the problem in the first place too.
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

Ok, sorry this wouldn't fit in a comment.

If you're on 2000 (edit -> I know you're not, just thought a future googler may be!), and schema/user equate to the same thing, then you will want to use sp_changeobjectowner:

sp_changeobjectowner 'raj.studentMaster', 'dbo'

If you're on 2005+ and you want to change the schema, then, as Grant say, use ALTER SCHEMA (although I would personally specify the object class)

ALTER SCHEMA [dbo] TRANSFER OBJECT::[raj].[studentMaster]

If you're on 2005+ and you want to change the owner, then you need to use ALTER AUTHORIZATION:

ALTER AUTHORIZATION ON OBJECT::[raj].[studentMaster] TO [dbo]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.