November 17, 2009 at 2:16 am
I have created one schema (Schm1) into database and trying to run the script in order to create tables under schema (Schm1). but problem is that, script has another schema inside it and when i run that script its giving me error like:
”
User does not have permission to perform this action.
Msg 2759, Level 16, State 0, Line 2
CREATE SCHEMA failed due to previous errors.”
E.g
I have schema say Schm1 under abc database. now I want to add some table under Schm1
Here is script:
– Create schema for common tables
create schema Schm2
;
– Create common tables
create table Schm2.Table1(
ID nvarchar(20) not null,
TYPE nvarchar(50) not null,
primary key(ID)
)
;
Can we add Schm2 schema under Schm1 schema? If yes then please suggest me how we can do that? what Kind of permissions we need?
Waiting for your reply!
Thanks in advance
November 17, 2009 at 2:43 am
No such thing as a nested schema. What are you trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 3:23 am
Thanks for reply!!
Let me explain you the problem in detail...
I have a database (MyDB) which has 3 schema say A,B,C. A schema has 20 tables, B has 30 tables and C has 25 tables respectively. In my team, we are three developers (Say tarun1, tarun2, tarun3) and unsing same db (MyDB) at our own PCs.
But now I have a common SQL server where I want to create only one database with 3 users (schemas) i.e. tarun1, tarun2, tarun3 for all 3 developers so that each can access single db using their own schemas(tarun1, tarun2, tarun3).
After we have 3 schemas as tarun1, tarun2, tarun3 our next step is to insert the tables into the respective schemas. and which mean we have to insert existing schemas A,B,C (with their tables ) into the newly created schema tarun1, tarun2, tarun3.
so the hierarchy should be like....
tarun1.A.Table1
I am getting an error while trying to add the existing schemas into newly created schema. error was
”User does not have permission to perform this action.
Msg 2759, Level 16, State 0, Line 2
CREATE SCHEMA failed due to previous errors.”
could this be possible in SQL server 2005? if so then how....
let me know if problem is still unclear to you... thanks in advance.
waiting for your reply!
November 17, 2009 at 3:59 am
tarunsoodrnd (11/17/2009)
After we have 3 schemas as tarun1, tarun2, tarun3 our next step is to insert the tables into the respective schemas. and which mean we have to insert existing schemas A,B,C (with their tables ) into the newly created schema tarun1, tarun2, tarun3.so the hierarchy should be like....
tarun1.A.Table1
You cannot create nested schemas. A schema cannot contain a schema and a table's name is database.schema.table. It cannot be database.schema.schema.table.
You're going to have to find a different way to do this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 17, 2009 at 4:08 am
Thanks a lot....
Can I achieve my requirement? can you suggest me any other way to do so...
any help will be appreciated!
Thanks in advance...
November 17, 2009 at 4:18 am
Separate databases for the 3 devs, or a single database with just the A, B, C schemas (and be careful not to mess up the other's work)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply