How do I map a SQL Server login to a user that already exists in a read-only database?

Question

One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one that they have access problems is shown as "Standby / Read-Only". The others are just normal.

So, I checked the Security -> Logins for the given principal and looked at the principal's properties to see if there were any user mappings to the read-only database for this login. There were no such mappings for that particular db while there were mappings for other databases and all with "db_datareader" role membership. Initially, I wanted to add a user mapping for this db as well; However, it failed with the below message:

"Failed to update database X because the database is read-only"

Then, I checked the read-only database and found that a user exists in there with the same name as the account that had this issue. It also had "db_datareader" role membership. So, I guess I just need to link the two; however, I don't know how. Please help me! Thanks.

Answer 1

Since the status of the database is Standby, I'm assuming this database is a Log Shipping secondary. If that's the case, the database cannot be written to at all on the secondary (making it writable would break Log Shipping).

Also, since you said that other databases (presumably not read-only) have correct mappings, I'm assuming you're using a SQL login. If that's the case, all you should have to do is recreate the login on the secondary server using CREATE LOGIN ... WITH SID to match the SID from the primary (look in sys.server_principals on the primary server to find it).

On the other hand, if you're using Active Directory for the login, you'll have to add the user principal to the primary database so that when it comes across to the secondary in a log backup, there's no need to do any remapping (note: depending on the situation, this may be a lot easier said than done!).

Answer 2

running this will normally map SQL user SPIDs but you will need to make the DB read/write for a while, preferably in single user mode.

Declare @SQL nvarchar(max)
Declare @Database nvarchar(max)

Declare @DBs table
(DBName nvarchar(100))

Declare @Databases varchar (max)
set @Databases = ''

select @Databases = coalesce (case when @Databases = ''                  
                            then name
                            else @Databases + ',' + name end,'')

from sys.databases Where database_id > 4 and name not like '%$%'
and name in ('MyDatabase')

Set @SQL = 'Select ''' + Replace(@Databases, ',', ''' Union Select ''') + ''''

Insert into @DBs
EXEC SP_ExecuteSql @SQLToExecute = @SQL

Declare @Logins table
(DB varchar(100)
,UserName sysname
,UserSID varbinary(85))

Declare @TempLogins table
(UserName sysname
,UserSID varbinary(85))

---
While Exists(Select * From @DBs)
Begin
    Set @Database = (Select Top 1 DBName From @DBs)

    Print 'Checking ' + @Database
    Set @SQL = 'exec [' + @Database + ']..sp_change_users_login @Action=''Report'' '

    Delete From @TempLogins
    Insert into @TempLogins
    EXEC SP_ExecuteSql @SQLToExecute = @SQL

    Insert into @Logins
    Select @Database, l.*
    From @TempLogins l

    Delete From @DBs Where DBName = @Database
End

Declare @User nvarchar(100)

While Exists(Select * From @Logins)
Begin
    Select Top 1 @Database = DB, @User = UserName From @Logins

    Set @SQL = 'exec [' + @Database + ']..sp_change_users_login @Action=''Auto_fix'', @UserNamePattern = ''' + @User + ''' '

    Begin Try
        EXEC SP_ExecuteSql @SQLToExecute = @SQL
        Print 'Mapped ' + @User + ' in ' + @Database
    End Try
    Begin Catch
        Print 'Failed to map ' + @User + ' in ' + @Database
    End Catch

    Delete From @Logins Where DB = @Database and UserName = @User
End

Or you could remove the user from the DB and re-map it.