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:
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?