Tail log backup query in log shipping reversal

Question

I am doing log shipping failover and failback configuration in SQL Server 2008 R2 environment. During the Failover, I am taking the tail log backup of primary database with query:

Script to backup Log:----------

DECLARE @filepath VARCHAR(200) 
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn' 
BACKUP LOG [DBNAME] 
TO DISK = @filePath WITH NO_TRUNCATE, NOFORMAT, INIT, 
NAME = N'DBNAME-Tail Log Backup', 
SKIP, NOUNLOAD, NORECOVERY, STATS = 10

And script for Log restore is:

Script to restore Log:--------

DECLARE @filepath VARCHAR(200) 
SET @filePath=N'\\ClusterName\SQL_Tail_Backup\DBNAME_Tail_Log.trn' 
RESTORE LOG [DBNAME] 
FROM DISK = @filepath WITH RECOVERY; 
GO 

But after making secondary database writable, my applications are not connecting to the database. While, trying backup and Restore from GUI works well. Please Help!

Answer 1

If I had to make a guess your database is just fine, but you never created the server principals (logins) associated with the database principals (users) in your database. It's deprecated but try this and see if you get a result.

EXEC sp_change_users_login 'report'

You can also try this piece of code:

USE dbname;
SELECT logins.name as logins, users.name as users, users.type_desc
FROM sys.database_principals users
LEFT OUTER JOIN sys.server_principals logins
    ON users.sid = logins.sid
WHERE users.type IN ('U','S','G')

If there are any NULLs in the logins column then that's probably your problem. Create the login (if it's a SQL ID then make sure you specify the correct SID).