Search code examples
c#sql-server-2008r2-express

SQL Server 2008 R2 Express : Login failed for user

I have an application in C#2010, a database in SQL Server 2008 R2 Express, I don't have any problem to connect to my database MyDB on my work PC where I developed the application. But when I tried to install it on an other PC, and after configure my sever like this :

USE [master] 
GO 

CREATE LOGIN [User1] WITH PASSWORD=N'Pass', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON 
GO 

EXEC master..sp_addsrvrolemember @loginame = N'User1', @rolename = N'sysadmin' 
GO 

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2 
GO

RESTORE DATABASE [MyDB] FROM DISK = N'pathtoMyDB\MyDB.bak' WITH FILE = 1,
NOUNLOAD, STATS = 10 
GO

I run the application, here my problem : when I try to connect to MyDB, I get this message :

Login failed for user 'WIN-ELKPAS5MS2O\User1'. Reason: Failed to open the explicitly specified database. [CLIENT: ]

Solution

  • You've created a login - that's fine, but that's on the server (instance) level. The login gives this account/user rights to connect to the server - that's just step 1, though.

    Now you're restoring a database - but you're not creating a user inside that database that corresponds to that login you've just created.

    So after the RESTORE DATABASE, you also need:

    USE MyDB
GO

CREATE USER User1 FOR LOGIN User1;
GO

    Now you have a user inside your freshly restored database that corresponds to that login - and now, hopefully, your application will work.