Please use FireFox to view this page

CREATE PROCEDURE sp_change_users_login @Action varchar(10) -- REPORT / UPDATE_ONE / AUTO_FIX ,@UserNamePattern sysname = Null ,@LoginName sysname = Null ,@Password sysname = Null AS -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES -- set nocount on declare @exec_stmt nvarchar(430) declare @ret int, @FixMode char(5), @cfixesupdate int, -- count of fixes by update @cfixesaddlogin int, -- count of fixes by sp_addlogin @dbname sysname, @loginsid varbinary(85), @110name sysname -- SET INITIAL VALUES -- select @dbname = db_name(), @cfixesupdate = 0, @cfixesaddlogin = 0 -- ERROR IF IN USER TRANSACTION -- if @@trancount > 0 begin raiserror(15289,-1,-1) return (1) end -- INVALIDATE USE OF SPECIAL LOGIN/USER NAMES -- if suser_sid(@LoginName) = 0x1 -- 'sa' begin raiserror(15287,-1,-1,@LoginName) return (1) end if user_id(@UserNamePattern) in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema begin raiserror(15287,-1,-1,@UserNamePattern) return (1) end -- HANDLE REPORT -- if upper(@Action) = 'REPORT' begin -- VALIDATE PARAMS -- if @UserNamePattern IS NOT Null or @LoginName IS NOT Null begin raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName) return (1) end -- GENERATE REPORT -- select UserName = name, UserSID = sid from sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null order by name return (0) end -- HANDLE UPDATE_ONE -- if upper(@Action) = 'UPDATE_ONE' begin -- CHECK PERMISSIONS -- if not is_member('db_owner') = 1 begin raiserror(15247,-1,-1) return (1) end -- ERROR IF PARAMS NULL -- if @UserNamePattern IS Null or @LoginName IS Null begin raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName) return (1) end -- VALIDATE PARAMS -- -- Can ONLY remap SQL Users to SQL Logins! Should be no need -- for re-mapping NT logins, and if you try, you'll mess up -- the user status bits! if not exists (select name from sysusers where name = @UserNamePattern -- match user name and issqluser = 1) -- must be sql user begin raiserror(15291,-1,-1,'User',@UserNamePattern) return (1) end select @loginsid = sid from master.dbo.syslogins where loginname = @LoginName -- match login name and isntname = 0 -- cannot use nt logins if @loginsid is null begin raiserror(15291,-1,-1,'Login',@LoginName) return (1) end -- ERROR IF SID ALREADY IN USE IN DATABASE -- if exists (select sid from sysusers where sid = @loginsid and name <> @UserNamePattern) begin raiserror(15063,-1,-1) return (1) end -- CHANGE THE USERS LOGIN (SID) -- update sysusers set sid = @loginsid, updatedate = getdate() where name = @UserNamePattern and issqluser = 1 and sid <> @loginsid -- FINALIZATION: REPORT (ONLY IF NOT SUCCESSFUL) AND EXIT -- if @@error <> 0 or @@rowcount <> 1 raiserror(15295,-1,-1, 0) return (0) end -- ERROR IF NOT AUTO_FIX -- if upper(@Action) <> 'AUTO_FIX' begin raiserror(15286,-1,-1,@Action) return (1) end -- HANDLE AUTO_FIX -- -- CHECK PERMISSIONS -- if not is_srvrolemember('sysadmin') = 1 begin raiserror(15247,-1,-1) return (1) end -- VALIDATE PARAMS -- if @UserNamePattern IS Null or @LoginName IS NOT Null begin raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName) return (1) end -- LOOP THRU ORPHANED USERS -- select @exec_stmt = 'DECLARE ms_crs_110_Users cursor global for select name from sysusers where name = N' + quotename( @UserNamePattern , '''')+ ' and issqluser = 1 and suser_sname(sid) is null' EXECUTE (@exec_stmt) OPEN ms_crs_110_Users WHILE (110=110) begin FETCH next from ms_crs_110_Users into @110name if (@@fetch_status <> 0) begin DEALLOCATE ms_crs_110_Users BREAK end -- IS NAME ALREADY IN USE? -- -- if suser_sid(@110name) is null if not exists(select * from master.dbo.syslogins where loginname = @110name) begin -- VALIDATE PARAMS -- if @Password IS Null begin raiserror(15290,-1,-1,@Action,@UserNamePattern,@LoginName) return (1) end -- ADD LOGIN -- execute @ret = sp_addlogin @110name, @Password, @dbname if @ret <> 0 or suser_sid(@110name) is null begin raiserror(15497,16,1,@110name) deallocate ms_crs_110_Users return (1) end select @FixMode = '1AddL' raiserror(15293,-1,-1,@110name) end ELSE begin Select @FixMode = '2UpdU' Raiserror(15292,-1,-1,@110name) end -- REPORT ERROR & CONTINUE IF DUPLICATE SID IN DB -- select @loginsid = suser_sid(@110name) if user_sid(@loginsid) is not null begin raiserror(15331,-1,-1,@110name) CONTINUE end -- UPDATE SYSUSERS ROW -- update sysusers set sid = @loginsid, updatedate = getdate(), status = (status & ~1) | 2 where name = @110name if @@error <> 0 begin raiserror(15498,17,127) deallocate ms_crs_110_Users return (1) end if @FixMode = '1AddL' Select @cfixesaddlogin = @cfixesaddlogin + 1 else Select @cfixesupdate = @cfixesupdate + 1 end -- loop 110 -- REPORT AND RETURN SUCCESS -- raiserror(15295,-1,-1,@cfixesupdate) raiserror(15294,-1,-1,@cfixesaddlogin) return (0) -- sp_change_users_login