Troubleshoot orphan users in SQL Server

Sometimes, you may need to change the owner of an object. This article contains a code sample that you can use to change ownership of objects. :
INF: SQL Server Procedure to Change Object Owner
also this seems to work: Change the owner of all db objects

The most common reason for ending up with orphan users is, moving databases from one server to another or from one domain to another. Restoring an old copy of master database can also result in this problem. Moving databases between servers is quite a common task. So, what's exactly resulting in orphan users?

Well, all SQL Server logins are stored in the system table sysxlogins (in master database). Whenever you create a new login (or grant access to a Windows account), a corresponding entry gets added to the sysxlogins table. So, you could say, each login has a corresponding row in sysxlogins table. In sysxlogins, each login has an associated SID (Security Identifier), which is of type varbinary(85).

How to troubleshoot orphan users in SQL Server databases?

Tags: SQL Server , Tech Tips