domingo, 28 de octubre de 2012

Automatically Mapping a user to a login: sp_change_users_login

Sometimes we need to restore Databases in other servers, and logins probably doesn't exist on new server.
The restored Database contains Users and permissions but it aren't mapped with Logins.

Microsoft provides stored procedure to map user with login.
If login doesn't exist, Auto_Fix option will create it.

USE Database;
EXEC sp_change_users_login 'Auto_Fix', 'USER', NULL, 'Password';

Otherwise, if you only need to map USER with LOGIN, use the Update_One option:

USE Database;
EXEC sp_change_users_login 'Update_One', 'USER', 'LOGIN';

I hope that helps you.

Changing default database backup folder for SQL Server

If you don't modify the default paths for Data, Logs and Backup's on SQL Server Installation, you can modify it then.

To Modify SQL Data or Log Directory, go to Server Properties, Database Settings, Database Default Locations (marked in red).

But, where can we change SQL Backup directory?

In Windows Registry.


This is only an example, probably you must change Instance Name

Backup Directory (See image below).

Installation Error: The MOF compiler could not connect with the WMI server.

The MOF compiler could not connect with the WMI server.
This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.

If this error ocurred when you install SQL Server 2008 R2, try to do this one:

  • Open console command (Run->CMD with administrator privileges).
  •  net stop winmgmt
  • Rename folder %windir%\System32\Wbem\Repository to other one, for backup purposes (for example _Repository).
  • net start winmgmt

Now, when you try to install again, it will work OK.