Login fails even though it exists when failing over to mirror I have mirroring set up on a production database.  I have a login setup on both the database serving the database and the mirror.  It has identical login information and configuration.

When I failover the database, the ASP.NET application fails over to the second database (it has a failover partner identified), but the login stops working.  For some reason after a bit it starts working.  When I fail back over to the original, production database, the ASP.NET application is kicked out again--even though I know the login exists on production because the application was able to login originally.

Here's the chain of events:


Production + Mirror have same login
Fail over to Mirror, login rejected.  Starts working for some mysterious reason.  
Mirror fails over to original production, login that was initially working now not working.
You need to ensure the SIDs of the logins are the same on both servers.  The only way to ensure they are the same is to script the login from the working server to the mirror server.   You cannot simply recreate the logins with the same name and password since the login will not match the user that exists in the database.  When you CREATE LOGIN you must use the SID = xxx option.

Perhaps the code below will help point you in the right direction.

/*
    Copy SQL login info for movement to another SQL server.
    Copies the SID and the Password hash.

    Run this piece on the source server, and copy the results to another query window 
    connected to the destination server.  Run the results on the destination server to 
    DROP and CREATE LOGINs with the same SIDs and PASSWORDS as the source server.
*/
DECLARE @txt varchar(max);
SET @txt = '';
SELECT @txt = @txt + 'DROP LOGIN ' + QUOTENAME(l.name) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'CREATE LOGIN ' + QUOTENAME(name) + CHAR(13) + CHAR(10) + CHAR(9) + 'WITH PASSWORD = ' + CONVERT(varchar(max), l.password_hash, 1) + ' HASHED'  + CHAR(13) + CHAR(10) + CHAR(9) + ', SID = ' + CONVERT(varchar(max), l.sid, 1) + CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_DATABASE = ' + QUOTENAME(l.default_database_name) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_logins l
WHERE l.principal_id &gt; 1
    AND l.is_disabled = 0;
SELECT @txt;

/*
    Look for orphaned USERS with a matching LOGIN
    Run the below SQL code in the same window used to run the code generated by the above SQL text.

    This code will look for and correct orphaned users.  Relies on the LOGIN name and the USER name 
    being identical.
*/
GO
DECLARE @dbname nvarchar(max);
DECLARE @txt nvarchar(max);
SET @dbname = '';
SET @txt = '';
DECLARE dbcur CURSOR LOCAL FORWARD_ONLY FOR
SELECT name
FROM sys.databases
WHERE database_id &gt; 4;
OPEN dbcur;
FETCH NEXT FROM dbcur INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @txt = @txt + 
    '
USE ' + QUOTENAME(@dbname) + ';
GO
DECLARE @txt nvarchar(max);
SET @txt = '''';
SELECT @txt = @txt + ''ALTER USER '' + QUOTENAME(dp.name) + '' WITH LOGIN = '' + QUOTENAME(dp.name) + '';''
FROM sys.database_principals dp
WHERE dp.type = ''S''
    AND sid NOT IN (SELECT sid FROM master.sys.sql_logins)
    AND name IN (SELECT name from master.sys.sql_logins);
IF @txt &lt;&gt; '''' EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @txt;
    ';
    FETCH NEXT FROM dbcur INTO @dbname;
END
CLOSE dbcur;
DEALLOCATE dbcur;
SELECT @txt;