Solved

User Auth Issue after Move of Db to another server

Posted on 2008-10-21
3
156 Views
Last Modified: 2011-09-20
Hi Experts,

I am getting a User unable to login error after moving a Db to another server.
I followed these instructions to move the Db files
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22807851.html

I have run
ALTER AUTHORIZATION ON DATABASE::MyDbTO [SA]

and now I am getting error:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'WebsiteUser'.

Any Ideas?
0
Comment
Question by:Craig Lambie
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
jinn_hnnl earned 250 total points
ID: 22765781
make sure the login user and the access right are restored too.
If you open the security and check for user who has right

//when you restore a backup database : fix login error
EXEC sp_change_users_login 'Auto_Fix', 'anyone', NULL, 'password'

when you attach db or moving to another server,  the login mapping to that user account probably became disassociated during the move.

USE DatabaseName;
   ALTER USER MyUserName WITH NAME = AnyName;
GO

You can look at this

http://stackoverflow.com/questions/176851/cannot-open-user-default-database-login-failed-sql-2005

0
 
LVL 1

Author Closing Comment

by:Craig Lambie
ID: 31508173
Thanks, that is exactly what I needed!
0
 
LVL 10

Expert Comment

by:jinn_hnnl
ID: 22773974
Glad to help ^^

JINN
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now