• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1303
  • Last Modified:

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

ALTER SCHEMA DBO
TRANSFER SALES.SALESPEOPLE
GO

Open in new window

0
danny1620
Asked:
danny1620
  • 5
  • 3
2 Solutions
 
jorge_torizCommented:
There is not default schema at database level, there is only a default schema per user.
0
 
jorge_torizCommented:
ALTER SCHEMA myTargetSchema
TRANSFER mySourceSchema.MyObject

myTargetSchema: The target schema where I want to put my object
mySourceShema: The schema where is currently my object
myObject: The object to transfer
0
 
danny1620Author Commented:
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
 
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
jorge_torizCommented:
First move your objects using the "ALTER SCHEMA ..." sentence, then create your desired roles, assign permissions to that roles and then enrole your users.
0
 
danny1620Author Commented:
Could please take a scenario and explain me because as said I was using the same Alter statement which gave me that Errors.. Thanks
0
 
jorge_torizCommented:
Do you have the shared memory enabled?
0
 
jorge_torizCommented:
Sorry, that answer wasn't for you :p

Haver you tried with other object?, your ALTER statement
0
 
danny1620Author Commented:
Yes jorge. I have tried the alter statement and that was the error msg that i got...
0
 
DarkHillCommented:
Are you trying to change the owner of the tables? If so, I've used the script below to do this in the past (please ensure you have a backup of the DB if this doesn't provide the desired effect):
DECLARE @old sysname, @new sysname, @sql varchar(1000)
 
SELECT
  @old = 'dbo'
  , @new = 'newuser'
  , @sql = '
  IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
  WHERE
      QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
      AND TABLE_SCHEMA = ''' + @old + '''
  )
  EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''
 
EXECUTE sp_MSforeachtable @sql

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now