Store Your Code Now
Tools: String Manipulations Find Dupes My IP Json Formatter Lorem Ipsum More...
Discussion Options

SQL Server: Find Orphaned or Missing Users Between Server and Database

8/4/2016 3:17:22 PM by: CodeLocker

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
View Copy: Content Copied
select
			dp.name [user_name]	
			,dp.type_desc [user_type]
			,isnull(sp.name,'Orhphaned!') [login_name]
			,sp.type_desc [login_type]
			,dp.type
			,dp.principal_id

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

where
			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]
Copy: Content Copied
×

Get Coding

Store your first code or add more code to your collection.
You must login to report abuse. ×