8/4/2016 7:17:22 PM

When you migrate a database to a new server, you may end up with a set of orphaned users. These users are part of the specific database but not part of the Db server.

To fix this, do the following:

  • Run the script below
  • For each user you need to fix, create the user at the server level (first Security folder in SSMS - not drilled down to a Db) with the same name as the orphaned user.
  • Run the Associate Users script at the bottom to associate the two users
			dp.name [user_name]	
			,dp.type_desc [user_type]
			,isnull(sp.name,'Orhphaned!') [login_name]
			,sp.type_desc [login_type]

			sys.database_principals dp
			left join sys.server_principals sp on (dp.sid = sp.sid)

			dp.type in ('S','U','G')
			and dp.principal_id > 4

order by sp.name

--associate users
--ALTER USER [my_user] WITH LOGIN = [my_user]