x

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?

more ▼

asked Feb 10, 2010 at 03:34 PM in Default

avatar image

FreeBird Rajesh
11 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Use the ALTER SCHEMA statement

ALTER SCHEMA dbo TRANSFER raj.studentMaster
more ▼

answered Feb 10, 2010 at 03:58 PM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

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.

Feb 10, 2010 at 04:15 PM TimothyAWiseman

Actually, I should have also mentioned how to create objects to avoid the problem in the first place too.

Feb 10, 2010 at 07:07 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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]
more ▼

answered Feb 10, 2010 at 04:22 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2020
x1096
x39

asked: Feb 10, 2010 at 03:34 PM

Seen: 5737 times

Last Updated: Feb 10, 2010 at 05:39 PM

Copyright 2016 Redgate Software. Privacy Policy