5

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:

enter image description here

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?

15

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.

6

@sepupic covers very well everything I would have suggested, but I thought I'd share the following script I run anytime I restore a database that was backed up from one SQL Server instance and restored to different one. Run it in the database you just restored. It basically automates what @sepupic is talking about. (The execute is commented out)

SET NOCOUNT ON

DECLARE @cmd nVARCHAR(max) = ''

SELECT @Cmd = @cmd + 'ALTER USER ' + dp.NAME + ' WITH LOGIN = ' + dp.NAME + ';' + CHAR(10)
FROM sys.database_principals dp
LEFT JOIN master.sys.server_principals sp ON sp.NAME = dp.NAME
WHERE dp.type = 's'
    AND dp.default_schema_name <> dp.NAME
    AND sp.NAME IS NOT NULL
ORDER BY dp.NAME

PRINT @Cmd
--EXECUTE sp_executeSQL @CMD
  • +1. This is the script (or very similar to it) we are using when restore our OLTP database on DWH server every night :) – sepupic Dec 14 '17 at 16:38
5

This sounds like an orphaned user. An orphaned user is where the user SID doesn't match the login SID, not the friendly name we see (likely to have been caused where a login was deleted from the server, or the database is restored from a different server). You should not need to delete the user and re-create it as all existing permissions would be lost (ok, this is only the role, but say you had individual permissions on other objects; stored procedures and tables etc). There is a feature to allow you to re-map the user to the login (even if it is of the same name).

use db1
GO
alter user [user_name] with login = [login_name]

so it would like like:

use wtest
GO
alter user [ls_readonly] with login = [ls_readonly]

See here for further details in MS documentation

-2

Drop the user from the database
Delete and re-create the user
Add the user to the database

You might not need the 2nd but it should not hurt

  • 5
    Don't do this. This way you will loose all the database permissions for these users – sepupic Dec 14 '17 at 14:46
  • 1
    @sepupic The permission is already lost so there is nothing to lose. I use this when I restore to a different server. – paparazzo Dec 14 '17 at 14:49
  • @Paparazzi, The permission is not 'lost', its orphaned. Say you had individual permissions on objects, not just a simple role re-creation. there could be hundreds of individual permissions. This feels like a nuclear option. – Simon Hellings Dec 14 '17 at 15:00
  • @sepupic Could be hundreds. This is one user and one permission and it works for me. – paparazzo Dec 14 '17 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.