Microsoft SQL Server – Orphaned users ()

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