January 11, 2012 at 2:13 am
Hi all,
Is there any ways to fix 200+ SQL orphan users and 100+ windows orphan users in a single go.
Thanks in advance.
January 11, 2012 at 3:45 am
You should find the info you're looking for here:
http://msdn.microsoft.com/en-us/library/ms175475.aspx
Hope this helps
Gianluca
-- Gianluca Sartori
January 11, 2012 at 4:03 am
But I think this is one by one process to fix orphan users.
Thanks
January 11, 2012 at 4:22 am
Well, first of all you have to decide what to do with your orphaned users. Fix? Delete?
For windows logins, you probably want to fix.
For SQL logins, you have to decide. Drop? Rebind?
-- Gianluca Sartori
January 11, 2012 at 4:26 am
I have to fix both types (windows, sql) of orphan users.
Thanks.
January 11, 2012 at 4:30 am
More information needed, please. Are all the users in the same database? Do all users have the same name as their respective logins?
John
January 11, 2012 at 5:15 am
Hi John
Yes all the users are in the same database. And all the users have same name as their respective logins.
Thanks.
January 11, 2012 at 6:15 am
did you try
Use Master
EXEC sp_change_users_login 'Auto_fix', 'userid'
Use USERDB
EXEC sp_change_users_login 'Auto_fix', 'userid'
This sometimes works for me after I've done a redirected restores to remap the users
January 11, 2012 at 6:21 am
Jpotucek (1/11/2012)
did you tryUse Master
EXEC sp_change_users_login 'Auto_fix', 'userid'
Use USERDB
EXEC sp_change_users_login 'Auto_fix', 'userid'
This sometimes works for me after I've done a redirected restores to remap the users
Yup, Auto fix should do the trick.
January 11, 2012 at 7:30 am
First, don't use sp_change_users_login. It's deprecated. And it doesn't support Windows Logins, or SQL Logins (one or the other, I can't remember which).
Second, since all the users are in the same database, and they have the same as their logins, it's a simple matter to generate the SQL to make the changes. Syntax and column names may not be exactly right, but this should give you the right idea.
SELECT 'ALTER USER ' + l.name + ' WITH LOGIN = ' + u.name
FROM MyDB.sys.database_principals u
JOIN master.sys.server_principals l
ON l.name = u.name
John
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply