Sage 500 Erp Login With Windows Authenticaion or Sql Authentication Updated FREE

Sage 500 Erp Login With Windows Authenticaion or Sql Authentication

Considering of the concern for potential problems (such as disability to perform security overrides ), I was moving users one past one to windows authentication so the scripts I take handle the conversion of a single user and in that location are some transmission steps involved.  I also used a dummy user as a placeholder for the person's settings being migrated.  These steps presume that the user's NT Login proper noun is the aforementioned every bit their current MAS500 User Name.  If that is non the case, the number of steps could be reduced since yous would not need the dummy user.

The steps are equally follows:

one) Place the tables in MAS500 that have a cavalcade named 'userid'.  I used this to identify which tables we demand to script in the the conversion stride.  Obvious shortcoming is if there are records in MAS500 that shop user related data but the FK related cavalcade is not named 'userid' I did not run across that consequence for our user group.  You besides may have to alter the Where clause in the Select Elevation i statement.  It intentially illiminates the '%wrk' and 'stg%' tables while the '%ksi' clause is to ignore any of our own custom tables which nosotros were not concerned with.

          
DECLARE @UserID As VARCHAR(50) DECLARE @TableName Every bit VARCHAR(100) DECLARE @CurColName AS VARCHAR(100) DECLARE @CurTableName AS VARCHAR(100) DECLARE @SQL AS VARCHAR(MAX) DECLARE @IncludeTableData AS Flake  SELECT @UserID = '?????' SELECT @IncludeTableData = 0  --1 include data in personalized tables, 0 only written report the table names  SELECT 'Tables with personalized information for: ' + @UserID  SELECT @CurTableName = '' WHILE ane=one 	BEGIN 		SELECT TOP ane @CurTableName = tab.proper name,  					@CurColName = col.name , 					@SQL = '' 		FROM sys.all_columns col  			  INNER Bring together sys.objects tab 					ON tab.object_id = col.object_id 			  INNER JOIN sys.types c 					ON c.system_type_id = col.system_type_id 		WHERE tab.blazon = 'U'  			AND col.name = 'userid' 			AND (tab.name NOT Like 'stg%' AND tab.proper noun Not Like '%ksi' AND tab.proper name NOT LIKE '%wrk' AND tab.name Not LIKE 'asp%') 			AND tab.proper name Like 'tsm%' 			AND tab.proper noun <> 'tsmUser' 			AND tab.name <> 'tsmUserSession' 			AND tab.Proper noun > @CurTableName 		ORDER BY tab.proper noun  		IF @@ROWCOUNT = 0 BREAK  	 		IF @IncludeTableData = 1  			SELECT @SQL = 'IF EXISTS(SELECT * FROM ' + @CurTableName + ' (nolock) WHERE ' +  @CurColName + ' = ''' + @UserID + ''') SELECT ''' + @CurTableName + '''' + ' as TABLE_NAME, * FROM ' + @CurTableName + ' (nolock) WHERE ' +  @CurColName + ' = ''' + @UserID + ''''	  		ELSE 			SELECT @SQL = 'IF EXISTS(SELECT * FROM ' + @CurTableName + ' (nolock) WHERE ' +  @CurColName + ' = ''' + @UserID + ''') SELECT DISTINCT ''' + @CurTableName + '''' + ' as TABLE_NAME FROM ' + @CurTableName + ' (nolock) WHERE ' +  @CurColName + ' = ''' + @UserID + ''' Lodge Past ane'	  		EXEC(@SQL) 		--PRINT @SQL 	Finish
        

two) Create a new dummy user through the UI (i.due east. 'TempUserForConversion').  This same dummy user tin be used for all other user conversions so y'all only have to do this step once.  If non using default security groups, yous will need to add together them to ane security group.

3) Temporarily motion all the personalized data for the user being transitioned to the dummy user.  The tables scripted beneath were the ones that nosotros needed to be concerned with, depending on what you find in Step 1, your listing may differ and you lot may demand to alter the script.

          
DECLARE @SrcUserID AS VARCHAR(50) DECLARE @TgtUserID Equally VARCHAR(l) SELECT @SrcUserID = '????' SELECT @TgtUserID = 'TempUserForConversion'  UPDATE tsmCustomForm SET UserID = @TgtUserID WHERE UserID = @SrcUserID  UPDATE tsmDataViewSetting SET ActualUserID = @TgtUserID, 	UserID = @TgtUserID WHERE ActualUserID = @SrcUserID  UPDATE tsmDataViewSettingUser SET ActualUserID = @TgtUserID, 	UserID = @TgtUserID WHERE ActualUserID = @SrcUserID  UPDATE tsmDataViewUser SET ActualUserID = @TgtUserID, 	UserID = @TgtUserID WHERE UserID = @SrcUserID  UPDATE tsmLookupView Ready ActualUserID = @TgtUserID, 	UserID = @TgtUserID WHERE UserID = @SrcUserID  UPDATE tsmRptSettingLast Set UserID = @TgtUserID WHERE UserID = @SrcUserID  UPDATE tsmUserCompanyGrp Fix UserID = @TgtUserID WHERE UserID = @SrcUserID 	AND UserGroupID <> 'zPublic'  --need to leave zPublic as all users require at least i security group  UPDATE tsmUserCompanyGrp SET UserID = @TgtUserID WHERE UserID = @SrcUserID 	AND CompanyID <> '???' --choose ane company  UPDATE tsmUserDesktop SET ActualUserID = @TgtUserID, 	UserID = @TgtUserID WHERE ActualUserID = @SrcUserID  UPDATE tsmUserLookupView Prepare ActualUserID = @TgtUserID, 	UserID = @TgtUserID WHERE UserID = @SrcUserID
        

4) Create the new business relationship (using windows authentication) for the user being transitioned through the UI.  Make sure they are assigned to the same security group that the dummy user was (since that particular security grouping is intentionally ignored by the script)

5) Modify the script from Pace 3 by switching the Tgt and Srs usernames and run it.  This volition move all the personalized information from the dummy user to the windows authenticated user

Promise the above helps.  It should at least give you something to compare against your procedure.  I'm sure there could be improvements fabricated to my process, I had but not gotten that far before realizing at that place was a major consequence.

Sage 500 Erp Login With Windows Authenticaion or Sql Authentication

DOWNLOAD HERE

Source: https://www.sagecity.com/us/sage500_erp/f/sage-500-erp-installation-and-technical-issues/31884/security-overrides-w-windows-authentication

Posted by: bryantbecouby.blogspot.com

Share :