Solved

User Auth Issue after Move of Db to another server

Posted on 2008-10-21
3
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Prologue It is often required to host multiple websites on a single instance of IIS, mostly in development environments instead of on production servers. I am sure it is not much a preferred solution on production servers but this is at least a pos…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

740 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