Why does user not have access to database?

I have a user ls_readonly which is supposed to have db_datareader privileges on several databases. I thought I had set it up right:

But when I connect to the server as ls_readonly and try to open the db in the Object Explorer, I get an error:


The database wtest is not accessible. (ObjectExplorer)

I open a query window in master and try run:

use wtest

This responds with:

Msg 916, Level 14, State 1, Line 1
The server principal "ls_readonly" is not able to access the database "wtest" under the current security context.

What am I missing?

UPDATE: here's a clue. If I delete ls_readonly as a user from under the database's security context, then I go to the user under the server's security context and under "User Mapping" I grant access to the database, then it starts working.

It could be that the database was originally restored from a different server, which also has a ls_readonly user. I guess then that the user identification is not based on the username?

It could be that the database was originally restored from a different
server, which also has a ls_readonly user. I guess then that the user
identification is not based on the username?

Normally, all the logins have the same sid with corresponding users. That is the sense of CREATE USER FROM LOGIN command: the database principal is created with the same sid (security identifier) that corresponding login has.

This user sid is now stored in your database, and when you backup/restore your database the sid is also preserved.

Imagine now that you have 2 servers and both of them have ls_readonly login. to make it simple, on the first server ls_readonly has sid = 1, on the second sid = 2. You have the database MyDB where this login is mapped. On the first server MyDB stores sid = 1, on the second sid = 2.

You backup MyDB database on the first server and restore it on the second. Now your login has sid = 2 and in the database MyDB it's sid = 1.

You log in as ls_readonly and try to access MyDB, server is checking if there is sid = 2 in the database, there is no, so for the server ls_readonly is not mapped to MyDB at all.

This can be fixed by doing

alter user ls_readonly with login = ls_readonly;

This command just update database's sid of ls_readonly user with the sid of ls_readonly login. All the permissions are preserved.

If you recreate user by dropping it, once you drop the user, all the permissions granted to it are dropped and you need to re-grant them.

StackExchange Database Administrators Q#193138, answer score: 15