February 10, 2012 at 7:45 am
I have always used autofix to take care of my orphaned sql logins, which now doesn't work in sql 2008, a little confused by the msdn explanation.
EXEC sp_change_users_login 'auto_fix', 'moe_user' no longer works
February 10, 2012 at 12:49 pm
Its still valid in SQL2008 but is marked to be deprecated. try alter user instead.
What error do you get?
---------------------------------------------------------------------
February 10, 2012 at 12:50 pm
Also I always use the 'update_one' option
---------------------------------------------------------------------
February 16, 2012 at 10:13 am
I still use it manually with no trouble, but I came up with this for when we restore production to a test environment.
EXEC sp_msforeachdb '
USE [?];
DECLARE @cmd NVARCHAR(MAX)
SELECT
@cmd = COALESCE( @cmd + '';ALTER USER ['' + [NAME] + ''] WITH LOGIN = ['' + [name] + '']'',''ALTER USER ['' + [NAME] + ''] WITH LOGIN = ['' + [name] + '']'')
FROM
sysusers
WHERE islogin = 1 AND sid IS NOT NULL AND sid ! = 0x00 AND name != ''dbo''
EXEC sp_executesql @cmd '
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply