Orphan Users
When a database is moved from one server to another "Orphan users" is one of those little things that tend to create problem in smooth transition.
There could be 2 reasons because of which Orphan users might get created
- Associated Login for the user itself isn't present on the server where database has been moved
- Mismatch between SIDs of USER of the database and the LOGIN of the server
When the database user looses it's association (SID mismatch) with Login it becomes orphan and this uncoupling leads to LOGIN loosing its privileges on that database.
In this tip we will try to build a script to address second reason mentioned above
sp_change_users_login 'report' - lists the orphan users from the databases
master.sys.server_principals / master.dbo.syslogins - lists the logins present on the server.
