question

arunsampath-4349 avatar image
0 Votes"
arunsampath-4349 asked OlafHelper-2800 commented

Login failed for user '<db_user>'. Reason: Failed to open the explicitly specified database '<DB1>'. [CLIENT: <local machine>]

I restored my production database "DB1" in development server. The database contains 'db_user' schema.
Same 'db_user' name i am using prod and dev.

After restoration, when 'db_users' trying to connect db, getting error "The Database DB1 is not accessible".

Also i checked in errorlog "Login failed for user 'db_user'. Reason: Failed to open the explicitly specified database 'DB1'. [CLIENT: <local machine>]"

i am getting error, when tried to grant access,

116678-image.png


sql-server-general
image.png (20.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered OlafHelper-2800 commented

Same 'db_user' name i am using prod and dev.

May the same name, but different SID's and that one is important. So you have an "orphaned user", a database user without a login matching by the SID.
=> Troubleshoot orphaned users (SQL Server)


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot for info,

since the same user already available, new sid has been created for the same user while restoring.
So I executed below statement in user db [DB01]

use [DB01];
alter user db_user with
login = db_user

Now user can access the DB01.

0 Votes 0 ·

If you are going to make repeated restores, you can avoid having to this every time by creating the logins using the WITH SID option. That is

CREATE LOGIN db_user WITH SID = 0x....

You would take the SID from the other server.

1 Vote 1 ·

Additional to Erland, you can generate script for the SQL login, see MS Support

Transfer logins and passwords between instances of SQL Server


0 Votes 0 ·