0

I took a backup and when I tried to restore it on another server, everything seemed to work perfectly, but I can not connect to it from any of my web applications or services.

I get this error:

System.Data.SqlClient.SqlException: Login failed for user 'domain\user'.

I use an Active Directory technical user in my web application.

I ran this line due to its user:

USE database;    
ALTER USER [domain\user] WITH LOGIN = [domain\user]

I tried restore it in SSMS and with this code, too:

USE master;
GO

RESTORE DATABASE database
FROM DISK = 'U:\BACKUP\database.bak'
WITH RECOVERY,  
MOVE 'database' TO 'U:\DATA\database.mdf',
MOVE 'database_log' TO 'V:\LOG\database_log.ldf';
GO

I added the db_datareader and the db_datawriter and even the db_owner roles to the technical user.

I do not know what the problem is.

UPDATE
The source SQL Server version is 11.0.6567.0, the target version is 11.0.7001.0.

UPDATE 2

I tried this:

EXEC sp_change_users_login 'Update_One', 'domain\user', 'domain\user'

but it throws this error:

Terminating this procedure. The User name 'domain\user' is absent or invalid.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sándor Hatvani
  • 435
  • 1
  • 7
  • 21
  • Check this: https://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename – CR241 Oct 22 '18 at 20:46

1 Answers1

1

You have to map login (sql server) to user (database).

After restore, do those command, in that order :

CREATE LOGIN [domain\user] from windows

and

USE database;    
ALTER USER [domain\user] WITH LOGIN = [domain\user]
DanB
  • 2,022
  • 1
  • 12
  • 24
  • Thank you for your answer but this line is not for that: ALTER USER [domain\user] WITH LOGIN = [domain\user] – Sándor Hatvani Oct 22 '18 at 20:31
  • 1
    On a new server, I think you have to create the login first. – DanB Oct 22 '18 at 20:40
  • The user came with the database but I tried to delete it and create a new one. It was useless. – Sándor Hatvani Oct 22 '18 at 20:45
  • 1
    Create the login (on the server), not the user (on the database). – DanB Oct 22 '18 at 20:48
  • 1
    @SándorHatvani: the security in SQL Server is **on two levels** - you have to have a **login** at the server level (and since this is at the **server** level, it's not part of your database backup!), and then you have a **user** (connected to that login) for each database he's allowed to use. So as Daniel has been telling you: you must **FIRST** create the **LOGIN** at the server level again, *before* you can reconnect the user in your database with that login ..... – marc_s Oct 22 '18 at 20:51
  • 1. I deleted the user than I run CREATE LOGIN [domain\user] from windows line. 2. I added db_datareader and db_datawriter to it on the datbase. 3. I run ALTER USER [domain\user] WITH LOGIN = [domain\user] line at the end. :( – Sándor Hatvani Oct 22 '18 at 21:05