SQL Server Schema creation f or existing database

Question

0.00/5 (No votes)

See more:

Hi,

I have created a database and all object like tables stored procedure and user defined table type under a default schema (dbo).

Now i want to create a new schema and to map all my stored procedure to New schema.
So that all changes to the table happens only through stored procedure and to avoid direct DML on my table.Also to map this schema to [NT AUTHORITY\NETWORK SERVICE] user role.

Thanks in advance.

Posted 26-May-13 21:38pm

vivek ayyakannu

Add a Solution

Comments

Arun Vasu 27-May-13 4:27am

try this
ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mycroft Holmes · Answer 1 · 2013-05-26T22:12:00

Solution 1

Create the new schema
Script all the procedures create to SSMS query
Search and replace on dbo replacing it with the new schema
run the script

You should now have 2 copies of all the procedures

Posted 26-May-13 22:12pm

Mycroft Holmes

gvprabu · Answer 2 · 2013-05-27T03:42:00

Hi
You can use Query like this...

SQL

ALTER SCHEMA the destination schema TRASFER Object::current schema.TableName;
For Example : ALTER SCHEMA dbo TRANSFER HWData.DesktopSoft_Master;

Check the following links...
Script to move all objects to a new schema for SQL Server[^]
How To Change SQL Server 2008/2005 Object Schema?[^]
Regards,
GVPrabu