Fix orphan users in MS SQL Server after restore

When you restore a MS SQL Server to a different machine, you might expect orphan users.
This also happens when you detach a database and then attach it on another server.

You see the users in the Security->Users section of the sql server and also in the database itself, but you can't login with it.

The problem is, that MS SQL stores the SID along the users, and when you restore it on another MS SQL server the link between user names and SID won't match any longer.

Fixing this via GUI is unfortunally not possible, when you try to map the user it trys to create a new sql user and fails, because there already exists such a user.

Fortunally there are a few stored procedures (available since sql 2000 version) which help you fix the logins.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

MS SQL Server 2008 R2 SP1 fails with error 84C4000E

Windows updates are fine, they make sure your system gets updated when needed.

Sometimes you get strange error numbers with the message that a update did fail.

Today we had a error 84C4000E when installing SP1 of SQL Express 2008R2 on a Windows 2008 Server.
Normaly google helps solving the problem, not (completely) this time:

The only reference to the error number I was able to find was:

http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/05050d46-d6f9-41e4-8276-46750dabe88f


Spanish... ok, google helps translating....

So it turned down to the following issue:

• Your windows server is installed/configured in a language not supported by SQL 2008R2
• Such "wrong" combinations are for example German-Swiss and trying to install a German edition of SQL 2008R2 (Or aparently also some variants of spanish)

The simplest thing to solve this:

• Just change your user locale from German-Swiss to German-Germany
• Install the service pack
• Change the user locale back to what is was previously

During installation we had similar problems and after some talk with MS support it did turn out that a German SQL Server 2008R2 can't be installed if the user locale was no German-Germany.
So changing it to the requested locale did the trick.

Strange enough, we later on wished to enter the product key... again the same problem and after more talk with MS support, changing the locale again did the trick...

According to MS support it should also be possible to change the locale to English-US and a german SQL Server should also be "installable"...