How to Change the Default Schema of an Existing DB
hi Guys,
I am trying to change the Schema name of an existing DB using the below Alter Code... but then I get an error saying
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'SALESPEOPLE', because it does not exist or you do not have permission.
The user is the Owner of the Database and I can clearly see the Object Named SALESPEOPLE in the tables list and the User Name with all the rights....
help me out... I might be doing it wrong so I would appreciate a Step by Step Process of creating a schema and then changing the Default Schema.... also how to check all the required user permissions..
Thanks
I am trying to change the Schema name of an existing DB using the below Alter Code... but then I get an error saying
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'SALESPEOPLE', because it does not exist or you do not have permission.
The user is the Owner of the Database and I can clearly see the Object Named SALESPEOPLE in the tables list and the User Name with all the rights....
help me out... I might be doing it wrong so I would appreciate a Step by Step Process of creating a schema and then changing the Default Schema.... also how to check all the required user permissions..
Thanks
ALTER SCHEMA DBO
TRANSFER SALES.SALESPEOPLE
GO
jorge_toriz
There is not default schema at database level, there is only a default schema per user.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jorge,,
I guess i am confused Let me tell you briefly what I actually need to do... I have a Database with 120 tables they have DBO prefix... Now I need to Partition the DB accordingly i.e., create and assign the tables to a particular filegroup and assign roles, User permissions to already existing users/Logins... How do i Proceed .. I am a newbie.. and I guess i was trying to do smething wrong in the previous situation ....Please Explain on how to proceed...
Thanks
I guess i am confused Let me tell you briefly what I actually need to do... I have a Database with 120 tables they have DBO prefix... Now I need to Partition the DB accordingly i.e., create and assign the tables to a particular filegroup and assign roles, User permissions to already existing users/Logins... How do i Proceed .. I am a newbie.. and I guess i was trying to do smething wrong in the previous situation ....Please Explain on how to proceed...
Thanks
First move your objects using the "ALTER SCHEMA ..." sentence, then create your desired roles, assign permissions to that roles and then enrole your users.
ASKER
Could please take a scenario and explain me because as said I was using the same Alter statement which gave me that Errors.. Thanks
Do you have the shared memory enabled?
Sorry, that answer wasn't for you :p
Haver you tried with other object?, your ALTER statement
Haver you tried with other object?, your ALTER statement
ASKER
Yes jorge. I have tried the alter statement and that was the error msg that i got...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.