Schema rename

Question

Schema rename

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
June 24, 2009 at 4:01 pm

#135738

Hi All,
Is it possible in SQL server to rename a schema ?
For eg. dbo.cnsmr
dbo.cnsmrAddress
dbo.facilityAddress
To
arc.cnsmr
arc.cnsmrAddress
arc.facilityaddress
and is it possible to assign a specific schema to a user so that if he creates a new table, that table is created by e.g arc..
Thanks,
\\K
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

George M Parker SSCertifiable Points: 5412 More actions · Answer 1

I believe you need to create the new schema and then transfer the object ownership of all existings objects to it. You can look up ALTER SCHEMA in books on line for the syntax to transfer ownership.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

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.

MJ

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 3

MANU (6/24/2009)
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.
MJ

Hi Manu,

Thanks for your help.

Quick question:

Lets say if I create a new schema "Arc" and I want to transfer dbo.test to Arc.test and a user called bob is using that dbo.test. Now what you are telling is that I should script out permissions of bob on dbo.test (does by default BOB has create table permission for dbo schema ?)

-- or --

when I create a new schema "Arc" using create schema Arc -- statement, after that I need to give

GRANT create table to BOB.

eg.

CREATE schema Arc --- this will create Arc

GRANT create table to BOB -- this will grant create permission to BOB (user)

ALTER USER BOB

WITH DEFAULT_SCHEMA = Arc --- Now what ever tables bob creates, it will have Arc.

GO

ALTER SCHEMA Arc TRANSFER dbo.test -- this will transfer dbo schema to Arc

Note: If bob is a sys admin then no matter what default schema is assigned, the table will be dbo.

Correct me if I am understanding wrong.

Thanks,

\\K 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Now what you are telling is that I should script out permissions of bob on dbo.test (does by default BOB has create table permission for dbo schema ? No )

Yes, you need to script out any explicit permisison assigned to bob on dbo.test.

-- or --

ALTER USER BOB

WITH DEFAULT_SCHEMA = Arc --- Now what ever tables bob creates, it will have Arc. -- YES

GO

ALTER SCHEMA Arc TRANSFER dbo.test -- this will transfer dbo schema to Arc -- YES

Note: If bob is a sys admin then no matter what default schema is assigned, the table will be dbo. -- YES

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 5

MANU (6/25/2009)
Now what you are telling is that I should script out permissions of bob on dbo.test (does by default BOB has create table permission for dbo schema ? No )
Yes, you need to script out any explicit permisison assigned to bob on dbo.test.
-- or --
ALTER USER BOB
WITH DEFAULT_SCHEMA = Arc --- Now what ever tables bob creates, it will have Arc. -- YES
GO
ALTER SCHEMA Arc TRANSFER dbo.test -- this will transfer dbo schema to Arc -- YES
Note: If bob is a sys admin then no matter what default schema is assigned, the table will be dbo. -- YES

Thanks a lot for the clarification....

This solved my doubt.

\\K 🙂

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂