Usage:
EXEC sp_fix_user_logins @DBName = <DataBase>, @Action = <ActionOption>, @User = <User Specification>
sp_fix_user_logins:
- Will work on SQL 7 thru SQL 2008 versions of SQL Server.
- Either reports or fixes lost and or orphaned users in the current database.
- Can report/fix users on one or all databases.
- Will NOT fix READ ONLY and Offline databases, and it will NOT remove Orphans that own oblects.
- Can automatically change object ownership if @ForceFix is set to 'FixLogin'
- Will change object ownership to dbo if FixLogin is indicated and no @Owner is provided
Orphan user: A user in the current database that has no corresponding login in master.
Lost user: A user in the current database that has a login in master but the sid is out of sync.
<DataBase> = The name of any valid user defined database, or '*' for all'
<ActionOption> =
Report or ReportAll: Displays a list of all lost or orphaned users
ReportLost: Displays a list of all lost users
ReportOrphans: Displays a list of all orphaned users
Fix or FixAll: Repairs all lost users (changes the sid) and removes all orphaned users that do not own objects.
FixLost: Repairs all lost users (changes the sid).
FixOrphans: Removes all orphaned users that do not own objects.
<User Specification> =
NULL or '*': All users
Anyhting else: A specific user only or you may use LIKE operators
<ForceFix> =
NULL : Do Not change object ownership for orphaned users
FixLogin : If an orphaned user is to be removed and owns objects, then change ownership of objects owned
<Owner> =
NULL : Change object ownership to dbo
<username> : If the orphaned user owns objects then change ownership of objects owned to this user
