sp_change_users_login Question
-
Relative SQL Server admin newbie here, with a quick question about sp_change_users_login:
I moved my logins and database from one server to another, and noticed that the users on my newly-moved-over database aren't associated with any login. I've been reading up on the sp_change_users_login SP, and it works for me for single users, no problem. My big problem is that I have 1700+ users in the db that I need to change! Any ideas, or am I missing something that sp_change.. already does?
Thanks,
Lester
-
Should fix them all, report how many were fixed. Are you running Auto fix?
-
Hi -
Yes, I am running it with Auto_fix, but if I don't specify a user after the auto_fix, I get:
Server: Msg 15290, Level 16, State 1, Procedure sp_change_users_login, Line 137
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('(null)', '(null)').
-
Make a script with a cursor, that loops through all the users and connects them with the right login. That is what works for me. I have normally not more than 80 users, but it works fine. I do not need to take care for each individually user.
--Script to resynch orphan SQL Server login IDs and database user IDs
EXEC sp_changedbowner <USE YOUR DBO>
DECLARE @usr_nam nvarchar(255)
,@dbs_nam nvarchar(255)
DECLARE orp_lgn_cur cursor for
SELECT UserName = name
FROM sysusers a
WHERE NOT EXISTS (SELECT name
FROM master..syslogins b
-- MS WHERE b.name = a.name)
WHERE b.name = a.name COLLATE database_default)
AND issqluser = 1
AND (sid is not null and sid <> 0x0)
AND suser_sname(sid) is null
DECLARE orp_usr_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1
AND (sid is not null and sid <> 0x0)
AND suser_sname(sid) is null
ORDER BY name
SELECT @dbs_nam = db_name()
EXEC sp_change_users_login report
OPEN orp_lgn_cur
FETCH NEXT FROM orp_lgn_cur INTO @usr_nam
WHILE (@@fetch_status = 0)
BEGIN
PRINT @usr_nam + ' login being added'
EXEC master..sp_addlogin @usr_nam, @usr_nam, @dbs_nam
FETCH NEXT FROM orp_lgn_cur INTO @usr_nam
END
CLOSE orp_lgn_cur
DEALLOCATE orp_lgn_cur
OPEN orp_usr_cur
FETCH NEXT FROM orp_usr_cur INTO @usr_nam
WHILE (@@fetch_status = 0)
BEGIN
PRINT @usr_nam + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @usr_nam, @usr_nam
FETCH NEXT FROM orp_usr_cur INTO @usr_nam
END
CLOSE orp_usr_cur
DEALLOCATE orp_usr_cur
EXEC sp_change_users_login report
-
you may choose to update your sysusers table directly.
1. sp_configure 'allow update system table ...', true
2. use userdatabase
go
update sysusers set suid=m.suid from master..syslogins m,sysusers u
where m.name=u.name
go
and see how many users get solved.
-
Thanks for the help on this guys
I actually found the mapsids util from MS linked from another post. It worked perfectly!
Here was the KB article:
http://support.microsoft.com/kb/240872
Thanks again...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply