Lunarpages Web Hosting Forum

Author Topic: MS SQL database schema and username mismatch  (Read 3469 times)

Offline belwoodcafe

  • Trekkie
  • **
  • Posts: 16
MS SQL database schema and username mismatch
« on: June 21, 2007, 09:09:40 PM »
Hello,

This is my story. I got a .bak file of the MS SQL database from my previous host. I proceeded to upload it to lunarpages. I created an empty database and cafeUser in Plesk, then used My Little Admin and the restore tool.

However, the problem arose when my database schema was not created by ‘dbo’. Instead all the tables, stored procedures and functions were owned by a specific user, named bakeryUser. This may be good for security purposes, but it is terrible for transporting a database to another host.

Because the database was from another host there was no way to change the schema to ‘dbo’. If it were ‘dbo’ then any username can be created to login to the database. But in my case, the database schema referencing the bakeryUser and the database user I created called cafeUser did not match.

So, my site did not function properly. I then deleted the database and user, then started over. I created the specific user bakeryUser in Plesk and then proceeded with the restore tool. Now my database schema matched my database username; both were bakeryUser.

Many of us are aware by now that there is a quirk in the My Little Admin and Plesk system. Often times after performing a database restore, your database user is deactivated so you can no longer login with it. The common fix is simply to delete that user and recreate it in Plesk.

However, in my case, this was not possible. Since my schema and user matched, it would not let me delete the user because it was attached to the schema.

From here it required lunarpages windows administrators to fix the login problem. But, that was like pulling teeth. It has been almost a week and this still has not been resolved by technical support.

Meanwhile, I discovered an alternate fix. I found a script that can change the database schema to ‘dbo’. Here is another post about it at: http://www.lunarforums.com/index.php/topic,41439.0.html. Now, I no longer have any more problems with username/schema.

The moral of the story is to make sure your database schema is ‘dbo’ if possible. Maybe others out there will be able to avoid the trap I encountered by reading this post first.

« Last Edit: June 21, 2007, 09:31:34 PM by belwoodcafe »

RandyT

  • Guest
Re: MS SQL database schema and username mismatch
« Reply #1 on: June 21, 2007, 09:17:13 PM »
Hey belwoodcafe,

Thanks so much for posting the resolution to your issue here in the forums. I am sure this will be very helpful to someone in the future.   :clap:



RandyT