Insert user to Database in Serv-U

4/16/2020 10:39 AM

4/16/2020 4:34 PM

If you are using database for your Serv-U users, you can use SQL Statement to insert users in Database. In this article, the script is provided but it would best to advise customer to seek assistance with their Database Administrator in running the scripts.
 

Serv-U Managed File Transfer & Serv-U FTP Server

Edit and Execute the MS SQL Script in the Database.

SQL script:

DECLARE @size integer --Ammount of users for insert
DECLARE @current integer = '1' --Start position for insert


--VAR_BLOCK START
DECLARE @log_id nvarchar(max) --login
DECLARE @passchangon nvarchar(max) = '567' --don't change
DECLARE @encpassmode nvarchar(max) = '1' --don't change
DECLARE @passutf8 nvarchar(max) = '1' --don't change
DECLARE @pass nvarchar(max) = 'dm59A4E3308B0EA3DE8A1BAFC3C3F5C581' --pass: password
DECLARE @fullname nvarchar(max) -- fullname
DECLARE @admtype nvarchar(max) -- admin type
DECLARE @allowlog nvarchar(max) = '1' -- allow always login
DECLARE @homedr nvarchar(max) = 'C:\tmp_u' --home directory
DECLARE @odbcver nvarchar(max) = '3' --don't change
DECLARE @quota nvarchar(max) = '0' --don't change

--TMP_VARS START
DECLARE @tmp_log_id nvarchar(max)
DECLARE @tmp_fullname nvarchar(max)
--TMP_VARS END

--VAR_BLOCK END

--SET_COUNTERS START
SET @current = 1
SET @size = 3
--SET_COUNTERS END

--ADD SYS_ADMINS START
SET @log_id = 'admin_'
SET @fullname = 'admin_name_'
SET @admtype = '2' -- 2 is SYS-ADMIN

WHILE (SELECT @current) <= @size
    begin
        SET @tmp_log_id = (@log_id + CONVERT(nvarchar(max), @current))
        SET @tmp_fullname = (@fullname + CONVERT(nvarchar(max), @current))

        INSERT INTO dbo.SUUsers (LoginID, PasswordChangedOn, PasswordEncryptMode, PasswordUTF8, Password, FullName, AdminType, AlwaysAllowLogin, HomeDir, ODBCVersion, Quota, Description)
        VALUES (@tmp_log_id, @passchangon, @encpassmode, @passutf8, @pass, @tmp_fullname, @admtype, @allowlog, @homedr, @odbcver, @quota, 'AUTO_GENERATED');

        INSERT INTO dbo.UserDirAccess (LoginID, SortIndex, Dir, Access)
        VALUES (@tmp_log_id, '1', @homedr, '7999') --7999 is full access with executable

        SET @current += 1
    end
--ADD SYS_ADMINS END

--SET_COUNTERS START
SET @current = 1
SET @size = 7
--SET_COUNTERS END

--ADD_USERS START
SET @log_id = 'user_'
SET @fullname = 'user_name_'
SET @admtype = NULL

WHILE (SELECT @current) <= @size
    begin
        SET @tmp_log_id = (@log_id + CONVERT(nvarchar(max), @current))
        SET @tmp_fullname = (@fullname + CONVERT(nvarchar(max), @current))

        INSERT INTO dbo.SUUsers (LoginID, PasswordChangedOn, PasswordEncryptMode, PasswordUTF8, Password, FullName, AdminType, AlwaysAllowLogin, HomeDir, ODBCVersion, Quota, Description)
        VALUES (@tmp_log_id, @passchangon, @encpassmode, @passutf8, @pass, @tmp_fullname, @admtype, @allowlog, @homedr, @odbcver, @quota, 'AUTO_GENERATED');

        INSERT INTO dbo.UserDirAccess (LoginID, SortIndex, Dir, Access)
        VALUES (@tmp_log_id, '1', @homedr, '7967') ----7967 is full access without executable

        SET @current += 1
    end
--ADD_USERS END

Disclaimer: Scripts are not supported under any SolarWinds support program or service. Scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.