Migrating Users

The time may come when you need to transfer users from one SLX database to another.

In WG Admin, you can export some of the user fields into a tab-delimited file (i.e., go to the Users view, right-click, and select Export…).

You can also import users … from a .csv file with an entirely different column-format than the export. So you can’t simply export from one (source) database, and use the same file to import into the target.

That import file must have not only the correct format, but a specific name (which you cannot change, though you can move/copy it to a different location). You’ll find the blank version of it in C:\Documents and Settings\All Users\Application Data\SalesLogix\SaleslogixUserImport.CSV, and the column list here.

To get the complete set of importable user data out of the source database, for users on a particular Team, run the following query against the database in SSMS:

SELECT ISNULL(usr.UserName, ''), -- USERNAME
USERTYPE = CASE usy.Type
     WHEN 'A' THEN 'Add On'
     WHEN 'C' THEN 'Concurrent'
     WHEN 'M' THEN 'Remote'
     WHEN 'N' THEN 'Network'
     WHEN 'R' THEN 'Retired'
     WHEN 'T' THEN 'Web Only'
     WHEN 'V' THEN 'WebViewer'
     ELSE 'Not Recognized' END, -- USERTYPE
ISNULL(usy.UserTemplate, ''), -- USERTEMPLATE
ISNULL(usr.Prefix, ''), -- PREFIX
ISNULL(usr.FirstName, ''), -- FIRSTNAME
ISNULL(usr.MiddleName, ''), -- MIDDLENAME
ISNULL(usr.LastName, ''), -- LASTNAME
ISNULL(usr.Suffix, ''), -- SUFFIX
ISNULL(usr.Email, ''), -- EMAIL
ISNULL(usr.Title, ''), -- TITLE
ISNULL(usr.Region, ''), -- REGION
ISNULL(usr.Division, ''), -- DIVISION
ISNULL(usr.Department, ''), -- DEPARTMENT
ISNULL('ADMIN', ''), -- MANAGER
     -- ISNULL(usy.ManagerID, ''), -- MANAGER
ISNULL(usy.IsManager, ''), -- ISMANAGER
ISNULL(wrk.Address1, ''), -- WORKADDRESS1
ISNULL(wrk.Address2, ''), -- WORKADDRESS2
ISNULL(wrk.City, ''), -- WORKCITY
ISNULL(wrk.State, ''), -- WORKSTATE
ISNULL(wrk.PostalCode, ''), -- WORKPOSTALCODE
ISNULL(wrk.County, ''), -- WORKCOUNTY
ISNULL(wrk.Country, ''), -- WORKCOUNTRY
ISNULL(usr.Phone, ''), -- WORKPHONE
ISNULL(usr.Direct, ''), -- DIRECTPHONE
ISNULL(usr.Fax, ''), -- FAX
ISNULL(usr.Mobile, ''), -- MOBILEPHONE
ISNULL(usr.PagerTerm, ''), -- PAGERNUMBER
ISNULL(usr.PINNumber, ''), -- PAGERPIN
ISNULL(adr.Address1, ''), -- HOMEADDRESS1
ISNULL(adr.Address2, ''), -- HOMEADDRESS2
ISNULL(adr.City, ''), -- HOMECITY
ISNULL(adr.State, ''), -- HOMESTATE
ISNULL(adr.PostalCode, ''), -- HOMEPOSTALCODE
ISNULL(adr.County, ''), -- HOMECOUNTY
ISNULL(adr.Country, ''), -- HOMECOUNTRY
ISNULL(usr.Home, ''), -- HOMEPHONE
ISNULL(usr.AccountingUserID, ''), -- ACCOUNTINGID
ISNULL(pro.AssetID, ''), -- ASSETID
ISNULL(pro.AssetDesc, ''), -- ASSETDESC
HireDate = CASE WHEN pro.HireDate IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.HireDate)
END,
BirthDate = CASE WHEN pro.BirthDate IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.BirthDate)
END,
ISNULL(pro.Spouse, ''), -- SPOUSE
ISNULL(pro.SSN, ''), -- SSN
ISNULL(pro.EmployeeNumber, ''), -- EMPLOYEENUMBER
USERDATE1 = CASE WHEN pro.USERDATE1 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE1)
END,
USERDATE2 = CASE WHEN pro.USERDATE2 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE2)
END,
USERDATE3 = CASE WHEN pro.USERDATE3 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE3)
END,
USERDATE4 = CASE WHEN pro.USERDATE4 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE4)
END,
USERDATE5 = CASE WHEN pro.USERDATE5 IS NULL THEN
	''
ELSE
	CONVERT(varchar(25), pro.USERDATE5)
END,
ISNULL(pro.USERTEXT1, ''), -- USERTEXT1
ISNULL(pro.USERTEXT2, ''), -- USERTEXT2
ISNULL(pro.USERTEXT3, ''), -- USERTEXT3
ISNULL(pro.USERTEXT4, ''), -- USERTEXT4
ISNULL(pro.USERTEXT5, ''), -- USERTEXT5
USERNUM1 = CASE WHEN pro.USERNUM1 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM1)
END,
USERNUM2 = CASE WHEN pro.USERNUM2 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM2)
END,
USERNUM3 = CASE WHEN pro.USERNUM3 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM3)
END,
USERNUM4 = CASE WHEN pro.USERNUM4 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM4)
END,
USERNUM5 = CASE WHEN pro.USERNUM5 IS NULL THEN
	''
ELSE
	CONVERT(varchar(20), pro.USERNUM5)
END,
CONVERT(varchar(max), ISNULL(pro.NOTES, '')), -- NOTES
ISNULL(usr.WindowsSID, ''), -- WINDOWSSID (Don't import if the target
    -- database is on a different network than the source database)
ISNULL(usr.UserCompany, '') -- USERCOMPANY
FROM sysdba.UserInfo usr
LEFT OUTER JOIN sysdba.UserProfile pro
ON usr.UserID = pro.UserID
LEFT OUTER JOIN sysdba.UserSecurity usy
ON usr.UserID = usy.UserID
LEFT OUTER JOIN sysdba.SecRights rig -- for Team export
ON usr.UserID = rig.AccessID -- for Team export
LEFT OUTER JOIN sysdba.SecCode sec -- for Team export
ON rig.SecCodeID = sec.SecCodeID -- for Team export
LEFT OUTER JOIN sysdba.Address adr
ON usr.HomeAddressID = adr.AddressID
LEFT OUTER JOIN sysdba.Address wrk
ON usr.AddressID = wrk.AddressID
WHERE sec.SecCodeDesc = 'MyTeam' -- Team name,
     -- for Team export
AND usy.Type <> 'W' -- ADMIN user
AND usy.Type <> 'P' -- OTB Template (All Access, etc.) users

Right-click over the result set in SSMS, and Select All; then right-click again, and Copy.

Open the SaleslogixUserImport.CSV file, and Paste the result set into it, below the header row. Then Save the workbook in its original name and format.

Then, in WG Admin, go to Insert -> Import Users -> From CSV File…, select the SaleslogixUserImport.CSV, and import it.

You can’t import Retired users directly, so if you need to import those (for data integrity) the easiest way is to first import all of the non-retired users (in a single .CSV file). If you then create a second .CSV file with just the Retired users, but change all of their USERTYPE column values to “Network” (say), then any of those users who don’t have licenses available will automatically get converted into Retired ones by the import.

The above won’t bring the UserSecurity.ManagerID across (since that won’t be the same UserID in the target database as it was in the source). So unless you want to get a lot fancier (i.e., building a UserID lookup table, to transform those values), you’ll need to update that field manually in the target database.

If you want to get all of the users (not just those on a particular Team) out via the above SQL, just delete the SecRights and SecCodeID table joins, and the “MyTeam” WHERE-clause.

Also, if the target database is on a different network than the source, don’t import the WindowsSID field. That field gets populated when you click “Use Windows Authentication” for the User in WG Admin.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: