In short: If you restore a database – which was backed up on a different server – and want to create a server login and map it to your database user, you may get an error message. Reason: the user already exists in your database and cannot not be re-created. So you have to find the orphaned user in your database;
use YOURDBNAME;
GO;
EXEC sp_change_users_login ‘Report’;
The result should be something like this:
UserName|UserSID dbo|0x6975A7DA42EE2440BDF292BF4D8D338F
You can now link the login to the db user or create a new Login with the resulted SID above (tested, and working):
CREATE LOGIN [YourSQLServerLogin] WITH PASSWORD = 0x02003A95B96423C3D260F29BE27C39B139426666AEA07E8B8EBA75FG8F4EABB7ACBEEAF9E5E3510A58735E396309ABF3E9DF047378352B8868A0FBDF0BD8EEFE66CAE6C14963 HASHED, SID = 0x6975A7DA42EE2450BDF222BC4D8D338F, DEFAULT_DATABASE = [master]
Link an existing Login to the db-user:
EXEC sp_change_users_login ‘update_one’, ‘db_login1’, ‘db_login1’;
Unfortunatelly ‘dbo’ is an unallowed argument to the procedure sp_change_users_login … – so i’ve done with creating a new user :-) Afterwards you can manage your Login like before via SQl-Statments and/ or SQL Server Management Studio. Additional Links:Understanding and dealing with orphaned users in a SQL Server database https://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/ http://www.codeproject.com/Articles/594134/How-to-Fix-Orphaned-SQL-Users https://msdn.microsoft.com/en-us/library/ms175475.aspx
Change the owner of the database:
SQL Server 2008 and earlier:
use somedb go sp_changedbowner [ @loginame = ] 'login'
SQL Server 2012 and alter:
use master go ALTER AUTHORIZATION ON database::[Test-DB] TO [someLogin]
Documentation on Alter Authorisation can be found here: https://msdn.microsoft.com/de-de/library/ms187359.aspx