June 3, 2012 at 11:54 pm
Hello,
I have taken a backup of X database from 2000 server and restored it in 2008 server with compatibility type 100.
I have used SSMS wizard to backup and restore database.
SQL server 2008 already had a SQL credential called Rep_Login.
X database already has a schema called Rep_Login owned by user Rep_Login.
In the new server I have set default schema for Rep_Login as Rep_Login.
Additionally Rep_Login owns the schema Rep_Login in the new server.
Still when I am trying to write a query I need to write query as schema_name.object_name instead of only object name
Ex:
select * from Time_sid is not working
but
select * from Rep_Login.time_sid works
We need the login to access objects without Schema reference.
The script for Login is as follows:
CREATE LOGIN [Rep_Login] WITH PASSWORD=N'password', DEFAULT_DATABASE= X, DEFAULT_LANGUAGE=[us_english]
Script for user in the database is as follows:
CREATE USER Rep_Login FOR LOGIN X WITH DEFAULT_SCHEMA=Rep_Login
Can you please help?
Please let me know if you need any other information.
June 4, 2012 at 7:23 am
What server role Rep_Login has? for all sysadmin default schema is set to 'dbo' even if you have set default schema for that user.
June 4, 2012 at 9:53 am
One thing I can see being a potential problem is that the database user and the login are not synced. When you restore from 2000 to 2008, the id of the user is different. Try running the following:
USE X
GO
ALTER USER Rep_Login WITH LOGIN = Rep_Login
GO
Jared
CE - Microsoft
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply