Migrasi Geodatabase SQL Server 2016 to SQL Server 2017 (ArcGIS Enetprise 10.8.1) #228

Publish Data Vektor

source 2016:

Enable geodatabase menggunakan ArcGIS Pro 3.5
Enable geodatabase ArcGIS Pro 2.6
- via vm https://proxmox.gisportal.id/
- login role : LDAP
- username : dpradana
- password :
Sql Server 2016 : database.gisportal.id:1434
- ip 10.100.1.75
- database : movearcgis
- username : rndarcgis
- password : geonet$1234
- database option
  
  before after
Sharing Folder

In Server Share On User
- Windwos + R
- \10.100.1.72
  - username : arcgis
  - password : geonet$123
- Keycodes : ada di sharing folder \\10.100.1.72\img
- Enable Geodatabase : https://git.gisportal.id/rbsetiawan/ArcGIS/issues/82

Error Database Connectoin

ArcGIS Pro 2.6
SQL Server 2016

Solusi

download sql server client / ODBC 17
https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver17

-install SQL Server ODBC

image.png

26 KiB

WhatsApp Image 2025-10-15 at 16.56.48_5357d081.jpg

44 KiB

image.png

31 KiB

Data vector dalam bentuk Point, Polyline dan Polygon sudah ada di SQL Server 2016, DB Name: movearcgis

Koneksi DB SQL Server 2016

Data Vector di Tabel of Content

image.png

23 KiB

image.png

749 KiB

Backup and Restore SQL Server 2016 to SQL Server 2017

Restore database mode :

Without enable geodatabase enable geodatabase

Backup from SSMS

image.png

48 KiB

image.png

48 KiB

image.png

42 KiB

restore No Enable Enterprise GeoDatabase

sql server 2016 (windows vm2)
- url : 10.100.1.76
- url : database.gisportal.id:1436
- database name : movearcgisNoEGB
- username : rndarcgis
- password : geonet$123
sql server 2022 (windows vm3)
- url : 10.100.1.77
- url : database.gisportal.id:1437
- database name : movearcgisNoEGB
- username : rndarcgis
- password : geonet$123
sql server 2017 (LINUX vm4)
- url : 10.100.1.25
- url : database.gisportal.id
- database name : movearcgisNoEGB
- username : arcgis
- password :

from sql server 2016 (windows vm1)	to sql server 2016 (windows vm2)	to sql server 2022 (windows vm3)	to sql server 2017 (linux vm2)

image.png

19 KiB

image.png

35 KiB

image.png

58 KiB

image.png

25 KiB

image.png

44 KiB

image.png

22 KiB

image.png

57 KiB

image.png

24 KiB

restore Enable Enterprise GeoDatabase

Result Error Restore to EGDB

restore database sql server 2016 ke database sql server lain yang sudah di enable geodatabase error karena terdapat table yang sama dan saling menimpa

sql server 2016 (windows vm2)
- url : 10.100.1.76
- url : database.gisportal.id:1436
- database name : movearcgis
- username : rndarcgis
- password : geonet$123
sql server 2022 (windows vm3)
- url : 10.100.1.77
- url : database.gisportal.id:1437
- database name : movearcgis
- username : rndarcgis
- password : geonet$123
sql server 2017 (LINUX vm4)
- url : 10.100.1.25
- url : database.gisportal.id
- database name : movearcgis
- username : arcgis
- password :

from sql server 2016 (windows vm1)	to sql server 2016 (windows vm2)	to sql server 2022 (windows vm3)	to sql server 2017 (linux vm2)
		FAILD IN USE

image.png

79 KiB

image.png

114 KiB

Setelah di restore ke SQL Server 2016, 2017 dan 2022 maka selanjutnya data tersebut dibuka menggunakan ArcGIS Pro versi 2.6.1 dan 3.5.2, Hasilnya:

SQL Server 2016:

SQL Server 2017:

SQL Server 2022:

Kesimpulannya, untuk SQL Server 2022 data bisa langsung digunakan. Hal ini ditandai dengan munculnya icon point, polyline dan polygon. Dan juga sudah langsung enable enterprise geodatabase.

image.png

49 KiB

image.png

52 KiB

image.png

12 KiB

image.png

52 KiB

Uji Coba berikutnya adalah dengan melakukan publish data menggunakan SQL 2017 yang sudah teregister ke ArcGIS Enterprise 11.4, lalu BackUp database tersebut dan Restore ke SQL 2022.

Register SQL 2017 ke Enterprise 11.4:

publish data vector, jadikan dashboard.

kemdian ubah resource sde yang sudah diregisterkan ke ArcGIS Enterprise melalui ArcGIS Portal.

Tambhkan titik pada ArcGIS Pro di SQL Server 2022, kemudian Check di Dashboard. Jika data berubah seusai dengan SQL Server 2022, maka proses migrasi berhasil.

Data pada SQL Server 2022 melalui ArcGIS Pro:

Data pada Dashboard Enterprise 11.4:

image.png

2.2 MiB

image.png

358 KiB

image_update_sde_connection.png

358 KiB

image.png

761 KiB

image.png

68 KiB

Requirement :

User sa

Step by step :

Jalankan query berikut pada SQL Server yang akan dimigrasikan :

sp_help_revlogin.sql

USE master;
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
    DROP PROCEDURE sp_hexadecimal;
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(514) OUTPUT
AS
BEGIN
    DECLARE @charvalue varchar(514), @i int, @length int, @hexstring char(16);
    SELECT @charvalue = '0x', @i = 1, @length = DATALENGTH(@binvalue), @hexstring = '0123456789ABCDEF';
    WHILE (@i <= @length)
    BEGIN
        DECLARE @tempint int = CONVERT(int, SUBSTRING(@binvalue,@i,1));
        SELECT @charvalue = @charvalue
          + SUBSTRING(@hexstring, (@tempint/16)+1, 1)
          + SUBSTRING(@hexstring, (@tempint%16)+1, 1);
        SET @i += 1;
    END
    SELECT @hexvalue = @charvalue;
END
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
    DROP PROCEDURE sp_help_revlogin;
GO
CREATE PROCEDURE sp_help_revlogin
(
    @login_name sysname = NULL  -- NULL = semua SQL logins (type = 'S'), exclude 'sa'
)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @name sysname,
            @password_hash varbinary(256),
            @sid varbinary(85),
            @defaultdb sysname,
            @is_disabled bit,
            @is_policy_checked bit,
            @is_expiration_checked bit,
            @pwdhex varchar(514),
            @sidhex varchar(514),
            @line nvarchar(max);

    PRINT '-- Generated on source: ' + @@SERVERNAME;
    PRINT 'GO';
    PRINT '';

    DECLARE c CURSOR FOR
    SELECT sp.name,
           sl.password_hash,
           sp.sid,
           sp.default_database_name,
           sp.is_disabled,
           ISNULL(sl.is_policy_checked,0),
           ISNULL(sl.is_expiration_checked,0)
    FROM sys.server_principals sp
    LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
    WHERE sp.type = 'S'
      AND sp.name <> 'sa'
      AND (@login_name IS NULL OR sp.name = @login_name)
    ORDER BY sp.name;

    OPEN c;
    FETCH NEXT FROM c INTO @name,@password_hash,@sid,@defaultdb,@is_disabled,@is_policy_checked,@is_expiration_checked;

    IF @@FETCH_STATUS = -1
    BEGIN
        PRINT '-- No login(s) found.';
        CLOSE c; DEALLOCATE c;
        RETURN;
    END

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @password_hash IS NULL SET @pwdhex = NULL ELSE EXEC sp_hexadecimal @password_hash, @pwdhex OUTPUT;
        EXEC sp_hexadecimal @sid, @sidhex OUTPUT;

        PRINT N'-- ============================================================';
        PRINT N'-- Login: ' + QUOTENAME(@name);

        -- Jika login belum ada di target -> CREATE dengan HASHED + SID
        PRINT N'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(@name,'''','''''') + N''')';
        PRINT N'BEGIN';

        IF @pwdhex IS NULL
            SET @line = N'    CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = NULL, SID = ' + @sidhex
        ELSE
            SET @line = N'    CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = ' + @pwdhex + N' HASHED, SID = ' + @sidhex;

        SET @line = @line
            + N', DEFAULT_DATABASE = ' + QUOTENAME(ISNULL(@defaultdb,'master'))
            + N', CHECK_POLICY = ' + CASE WHEN @is_policy_checked=1 THEN N'ON' ELSE N'OFF' END
            + N', CHECK_EXPIRATION = ' + CASE WHEN @is_expiration_checked=1 THEN N'ON' ELSE N'OFF' END + N';';
        PRINT @line;

        IF @is_disabled = 1
            PRINT N'    ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE;';

        PRINT N'END';
        PRINT N'ELSE';
        PRINT N'BEGIN';
        PRINT N'    DECLARE @t_sid varbinary(85) = (SELECT sid FROM sys.server_principals WHERE name = N''' + REPLACE(@name,'''','''''') + N''');';
        PRINT N'    IF @t_sid = ' + @sidhex;
        PRINT N'    BEGIN';
        PRINT N'        -- Login already exists on target with SAME SID.';
        PRINT N'        -- Password hash cannot be applied via ALTER LOGIN; it only accepts clear text.';
        PRINT N'        -- If you still need to enforce the source password hash, drop & create using the block below.';
        PRINT N'        -- Optionally, keep policies in sync:';
        PRINT N'        ALTER LOGIN ' + QUOTENAME(@name) + N' WITH CHECK_POLICY = ' + CASE WHEN @is_policy_checked=1 THEN N'ON' ELSE N'OFF' END
            + N', CHECK_EXPIRATION = ' + CASE WHEN @is_expiration_checked=1 THEN N'ON' ELSE N'OFF' END + N';';
        IF @is_disabled = 1
            PRINT N'        ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE;';
        ELSE
            PRINT N'        ALTER LOGIN ' + QUOTENAME(@name) + N' ENABLE;';
        PRINT N'    END';
        PRINT N'    ELSE';
        PRINT N'    BEGIN';
        PRINT N'        PRINT N''WARNING: Login exists on target but SID differs from source: ' + REPLACE(@name,'''','''''') + N''';';
        PRINT N'        PRINT N''Review and run the commented DROP/CREATE below if you need to align SID & password with source.'';';
        PRINT N'        /*';
        PRINT N'        IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(@name,'''','''''') + N''')';
        PRINT N'            DROP LOGIN ' + QUOTENAME(@name) + N';';
        IF @pwdhex IS NULL
            SET @line = N'        CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = NULL, SID = ' + @sidhex
        ELSE
            SET @line = N'        CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = ' + @pwdhex + N' HASHED, SID = ' + @sidhex;
        SET @line = @line
            + N', DEFAULT_DATABASE = ' + QUOTENAME(ISNULL(@defaultdb,'master'))
            + N', CHECK_POLICY = ' + CASE WHEN @is_policy_checked=1 THEN N'ON' ELSE N'OFF' END
            + N', CHECK_EXPIRATION = ' + CASE WHEN @is_expiration_checked=1 THEN N'ON' ELSE N'OFF' END + N';';
        PRINT @line;
        IF @is_disabled = 1
            PRINT N'        ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE;';
        PRINT N'        */';
        PRINT N'    END';
        PRINT N'END';
        PRINT N'GO';
        PRINT N'';

        FETCH NEXT FROM c INTO @name,@password_hash,@sid,@defaultdb,@is_disabled,@is_policy_checked,@is_expiration_checked;
    END

    CLOSE c; DEALLOCATE c;
    PRINT N'-- End of generated login blocks.';
END
GO

sp_generate_login_for_dbs.sql

USE master;
GO

IF OBJECT_ID('sp_generate_logins_for_dbs') IS NOT NULL 
    DROP PROCEDURE sp_generate_logins_for_dbs;
GO

CREATE PROCEDURE sp_generate_logins_f******or_dbs
(
    @DatabaseList NVARCHAR(MAX)  -- Example: 'DB1,DB2,DB3'
)
AS
BEGIN
    SET NOCOUNT ON;

    --------------------------------------------------------------------
    -- Parse database list
    --------------------------------------------------------------------
    DECLARE @DBs TABLE (DBName sysname);
    INSERT INTO @DBs
        SELECT LTRIM(RTRIM(value))
        FROM string_split(@DatabaseList, ',');

    --------------------------------------------------------------------
    -- Collect unique logins used in specified DBs
    --------------------------------------------------------------------
    IF OBJECT_ID('tempdb..#Users') IS NOT NULL DROP TABLE #Users;
    CREATE TABLE #Users (LoginName sysname PRIMARY KEY);

    DECLARE @DB sysname, @sql NVARCHAR(MAX);

    DECLARE db_cursor CURSOR FOR SELECT DBName FROM @DBs;
    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @DB;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = N'
            INSERT INTO #Users(LoginName)
            SELECT DISTINCT dp.name
            FROM [' + @DB + '].sys.database_principals dp
            JOIN master.sys.server_principals sp ON dp.sid = sp.sid
            WHERE dp.type = ''S''            -- SQL authenticated users only
              AND dp.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
              AND dp.authentication_type = 1 -- SQL logins only
        ';

        EXEC(@sql);

        FETCH NEXT FROM db_cursor INTO @DB;
    END

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    --------------------------------------------------------------------
    -- Output login scripts through new sp_help_revlogin
    --------------------------------------------------------------------
    PRINT '------------------------------------------------------------';
    PRINT '-- LOGIN MIGRATION SCRIPT FOR DATABASES: ' + @DatabaseList;
    PRINT '-- Run the output below on TARGET server';
    PRINT '------------------------------------------------------------';
    PRINT '';
    
    DECLARE @Login sysname;

    DECLARE login_cursor CURSOR FOR SELECT LoginName FROM #Users ORDER BY LoginName;
    OPEN login_cursor;
    FETCH NEXT FROM login_cursor INTO @Login;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT '/* ===== LOGIN: ' + @Login + ' ===== */';
        EXEC sp_help_revlogin @Login;
        PRINT '';
        
        FETCH NEXT FROM login_cursor INTO @Login;
    END

    CLOSE login_cursor;
    DEALLOCATE login_cursor;
END
GO

Pastikan Stored Procedure berhasil ter generate. Lihat pada database master -> Programmability -> Stored Procedure. Jika belum muncul coba refresh database.

Jika sudah stored procedure sudah berhasil ter generate. Jalankan stored procedure dengan menggunakan query berikut.

run_generate_logins_for_dbs.sql

EXEC sp_generate_logins_for_dbs 'DB1,DB2'

Notes : DB1, DB2 adalah nama database

Copy hasilnya, pindahkan pada code editor / text editor seperti vs code dan notepad++. Save jika diperlukan.

Buka koneksi ke SQL Server tujuan migrasi.
Migrasikan databasenya terlebih dahulu.
Setelah seluruh database berhasil di migrasikan, jalankan query generate user yang sudah dibuat sebelumnya.

Jika query berhasil di jalankan, cek pada bagian user login, user akan tampil.
Test login dengan user yang sudah di migrasikan.
Jalankan query berikut untuk mapping user database dengan user login yang sudah di migrasikan :

***run_auto_map_db_users_to_logins.sql ***

/* ============================================================
   Auto Map DB Users to Logins (strict name match)
   Target  : SQL Server 2022 (run on TARGET server)
   Behavior: 
     - Only SQL users (dp.type='S', authentication_type=1)
     - Only SQL logins (sp.type='S')
     - If login with same name exists:
         * If SID mismatch -> ALTER USER ... WITH LOGIN = [name]
         * If SID match     -> no change
     - Report per user: FOUND / NOT FOUND (by presence of matching login)
   ============================================================ */

-- 1) SET YOUR DATABASE LIST HERE
DECLARE @DatabaseList NVARCHAR(MAX) = N'gis,PGE_GeoDB';  -- e.g. N'Sales,HR,Inventory'

-- 2) Parse DB list
DECLARE @DBs TABLE (DBName sysname PRIMARY KEY);
INSERT INTO @DBs(DBName)
SELECT LTRIM(RTRIM(value))
FROM string_split(@DatabaseList, ',');

-- 3) Report table
IF OBJECT_ID('tempdb..#Report') IS NOT NULL DROP TABLE #Report;
CREATE TABLE #Report
(
    db_name   sysname,
    user_name sysname,
    status    varchar(16) -- FOUND / NOT FOUND
);

-- 4) Loop each DB and process
DECLARE @DB sysname, @sql nvarchar(max);

DECLARE c CURSOR FOR SELECT DBName FROM @DBs;
OPEN c;
FETCH NEXT FROM c INTO @DB;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF DB_ID(@DB) IS NULL
    BEGIN
        PRINT CONCAT('WARNING: Database not found: ', @DB);
        FETCH NEXT FROM c INTO @DB;
        CONTINUE;
    END

    -- Dynamic SQL per database
    SET @sql = N'
    SET NOCOUNT ON;

    -- Collect users & matching logins by SAME NAME
    IF OBJECT_ID(''tempdb..#U'') IS NOT NULL DROP TABLE #U;
    CREATE TABLE #U
    (
        user_name sysname PRIMARY KEY,
        has_login bit NOT NULL,
        sid_mismatch bit NOT NULL
    );

    ;WITH dbusers AS
    (
        SELECT dp.name,
               dp.sid,
               dp.type,
               dp.authentication_type
        FROM ' + QUOTENAME(@DB) + N'.sys.database_principals AS dp
        WHERE dp.type = ''S''                                  -- SQL_USER
          AND dp.authentication_type = 1                      -- INSTANCE (server) auth
          AND dp.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'')
    )
    INSERT INTO #U(user_name, has_login, sid_mismatch)
    SELECT du.name AS user_name,
           CASE WHEN sp.principal_id IS NOT NULL THEN 1 ELSE 0 END AS has_login,
           CASE 
             WHEN sp.principal_id IS NULL THEN 0
             WHEN du.sid IS NULL OR sp.sid IS NULL THEN 0
             WHEN du.sid <> sp.sid THEN 1 ELSE 0 
           END AS sid_mismatch
    FROM dbusers du
    LEFT JOIN master.sys.server_principals sp
           ON sp.name = du.name
          AND sp.type = ''S''               -- SQL login only (skip Windows/Cert/Asym)
    ;

    -- Build ALTER statements for SID mismatch (strict name match)
    DECLARE @alter nvarchar(max) = N'''';

    SELECT @alter = STRING_AGG(
        N''USE ' + QUOTENAME(@DB) + N'; ALTER USER '' + QUOTENAME(user_name) + N'' WITH LOGIN = '' + QUOTENAME(user_name) + N'';'',
        CHAR(10)
    )
    FROM #U
    WHERE has_login = 1
      AND sid_mismatch = 1;

    IF @alter IS NOT NULL AND LEN(@alter) > 0
    BEGIN
        PRINT N''-- Applying SID remap (ALTER USER ... WITH LOGIN = <same name>) in ' + QUOTENAME(@DB) + N'...'';
        EXEC(@alter);
    END

    -- Insert report rows: FOUND (login exists by same name), NOT FOUND otherwise
    INSERT INTO #Report(db_name, user_name, status)
    SELECT N''' + REPLACE(@DB,'''','''''') + N''' AS db_name,
           user_name,
           CASE WHEN has_login = 1 THEN ''FOUND'' ELSE ''NOT FOUND'' END AS status
    FROM #U;

    DROP TABLE #U;
    ';

    EXEC(@sql);

    FETCH NEXT FROM c INTO @DB;
END

CLOSE c;
DEALLOCATE c;

-- 5) Final report
SELECT db_name, user_name, status
FROM #Report
ORDER BY db_name, user_name;

Jika hasil FOUND maka user berhasil ditemukan di user logins dan berhasil dimappingkan.

# Migrasi User Login ----- ### Requirement : * **User sa** ----- ### Step by step : 1. Jalankan query berikut pada SQL Server yang akan dimigrasikan : ***sp_help_revlogin.sql*** ``` USE master; GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal; GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar(514) OUTPUT AS BEGIN DECLARE @charvalue varchar(514), @i int, @length int, @hexstring char(16); SELECT @charvalue = '0x', @i = 1, @length = DATALENGTH(@binvalue), @hexstring = '0123456789ABCDEF'; WHILE (@i <= @length) BEGIN DECLARE @tempint int = CONVERT(int, SUBSTRING(@binvalue,@i,1)); SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, (@tempint/16)+1, 1) + SUBSTRING(@hexstring, (@tempint%16)+1, 1); SET @i += 1; END SELECT @hexvalue = @charvalue; END GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin; GO CREATE PROCEDURE sp_help_revlogin ( @login_name sysname = NULL -- NULL = semua SQL logins (type = 'S'), exclude 'sa' ) AS BEGIN SET NOCOUNT ON; DECLARE @name sysname, @password_hash varbinary(256), @sid varbinary(85), @defaultdb sysname, @is_disabled bit, @is_policy_checked bit, @is_expiration_checked bit, @pwdhex varchar(514), @sidhex varchar(514), @line nvarchar(max); PRINT '-- Generated on source: ' + @@SERVERNAME; PRINT 'GO'; PRINT ''; DECLARE c CURSOR FOR SELECT sp.name, sl.password_hash, sp.sid, sp.default_database_name, sp.is_disabled, ISNULL(sl.is_policy_checked,0), ISNULL(sl.is_expiration_checked,0) FROM sys.server_principals sp LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id WHERE sp.type = 'S' AND sp.name <> 'sa' AND (@login_name IS NULL OR sp.name = @login_name) ORDER BY sp.name; OPEN c; FETCH NEXT FROM c INTO @name,@password_hash,@sid,@defaultdb,@is_disabled,@is_policy_checked,@is_expiration_checked; IF @@FETCH_STATUS = -1 BEGIN PRINT '-- No login(s) found.'; CLOSE c; DEALLOCATE c; RETURN; END WHILE @@FETCH_STATUS = 0 BEGIN IF @password_hash IS NULL SET @pwdhex = NULL ELSE EXEC sp_hexadecimal @password_hash, @pwdhex OUTPUT; EXEC sp_hexadecimal @sid, @sidhex OUTPUT; PRINT N'-- ============================================================'; PRINT N'-- Login: ' + QUOTENAME(@name); -- Jika login belum ada di target -> CREATE dengan HASHED + SID PRINT N'IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(@name,'''','''''') + N''')'; PRINT N'BEGIN'; IF @pwdhex IS NULL SET @line = N' CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = NULL, SID = ' + @sidhex ELSE SET @line = N' CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = ' + @pwdhex + N' HASHED, SID = ' + @sidhex; SET @line = @line + N', DEFAULT_DATABASE = ' + QUOTENAME(ISNULL(@defaultdb,'master')) + N', CHECK_POLICY = ' + CASE WHEN @is_policy_checked=1 THEN N'ON' ELSE N'OFF' END + N', CHECK_EXPIRATION = ' + CASE WHEN @is_expiration_checked=1 THEN N'ON' ELSE N'OFF' END + N';'; PRINT @line; IF @is_disabled = 1 PRINT N' ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE;'; PRINT N'END'; PRINT N'ELSE'; PRINT N'BEGIN'; PRINT N' DECLARE @t_sid varbinary(85) = (SELECT sid FROM sys.server_principals WHERE name = N''' + REPLACE(@name,'''','''''') + N''');'; PRINT N' IF @t_sid = ' + @sidhex; PRINT N' BEGIN'; PRINT N' -- Login already exists on target with SAME SID.'; PRINT N' -- Password hash cannot be applied via ALTER LOGIN; it only accepts clear text.'; PRINT N' -- If you still need to enforce the source password hash, drop & create using the block below.'; PRINT N' -- Optionally, keep policies in sync:'; PRINT N' ALTER LOGIN ' + QUOTENAME(@name) + N' WITH CHECK_POLICY = ' + CASE WHEN @is_policy_checked=1 THEN N'ON' ELSE N'OFF' END + N', CHECK_EXPIRATION = ' + CASE WHEN @is_expiration_checked=1 THEN N'ON' ELSE N'OFF' END + N';'; IF @is_disabled = 1 PRINT N' ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE;'; ELSE PRINT N' ALTER LOGIN ' + QUOTENAME(@name) + N' ENABLE;'; PRINT N' END'; PRINT N' ELSE'; PRINT N' BEGIN'; PRINT N' PRINT N''WARNING: Login exists on target but SID differs from source: ' + REPLACE(@name,'''','''''') + N''';'; PRINT N' PRINT N''Review and run the commented DROP/CREATE below if you need to align SID & password with source.'';'; PRINT N' /*'; PRINT N' IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N''' + REPLACE(@name,'''','''''') + N''')'; PRINT N' DROP LOGIN ' + QUOTENAME(@name) + N';'; IF @pwdhex IS NULL SET @line = N' CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = NULL, SID = ' + @sidhex ELSE SET @line = N' CREATE LOGIN ' + QUOTENAME(@name) + N' WITH PASSWORD = ' + @pwdhex + N' HASHED, SID = ' + @sidhex; SET @line = @line + N', DEFAULT_DATABASE = ' + QUOTENAME(ISNULL(@defaultdb,'master')) + N', CHECK_POLICY = ' + CASE WHEN @is_policy_checked=1 THEN N'ON' ELSE N'OFF' END + N', CHECK_EXPIRATION = ' + CASE WHEN @is_expiration_checked=1 THEN N'ON' ELSE N'OFF' END + N';'; PRINT @line; IF @is_disabled = 1 PRINT N' ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE;'; PRINT N' */'; PRINT N' END'; PRINT N'END'; PRINT N'GO'; PRINT N''; FETCH NEXT FROM c INTO @name,@password_hash,@sid,@defaultdb,@is_disabled,@is_policy_checked,@is_expiration_checked; END CLOSE c; DEALLOCATE c; PRINT N'-- End of generated login blocks.'; END GO ``` ***sp_generate_login_for_dbs.sql*** ``` USE master; GO IF OBJECT_ID('sp_generate_logins_for_dbs') IS NOT NULL DROP PROCEDURE sp_generate_logins_for_dbs; GO CREATE PROCEDURE sp_generate_logins_f******or_dbs ( @DatabaseList NVARCHAR(MAX) -- Example: 'DB1,DB2,DB3' ) AS BEGIN SET NOCOUNT ON; -------------------------------------------------------------------- -- Parse database list -------------------------------------------------------------------- DECLARE @DBs TABLE (DBName sysname); INSERT INTO @DBs SELECT LTRIM(RTRIM(value)) FROM string_split(@DatabaseList, ','); -------------------------------------------------------------------- -- Collect unique logins used in specified DBs -------------------------------------------------------------------- IF OBJECT_ID('tempdb..#Users') IS NOT NULL DROP TABLE #Users; CREATE TABLE #Users (LoginName sysname PRIMARY KEY); DECLARE @DB sysname, @sql NVARCHAR(MAX); DECLARE db_cursor CURSOR FOR SELECT DBName FROM @DBs; OPEN db_cursor; FETCH NEXT FROM db_cursor INTO @DB; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N' INSERT INTO #Users(LoginName) SELECT DISTINCT dp.name FROM [' + @DB + '].sys.database_principals dp JOIN master.sys.server_principals sp ON dp.sid = sp.sid WHERE dp.type = ''S'' -- SQL authenticated users only AND dp.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') AND dp.authentication_type = 1 -- SQL logins only '; EXEC(@sql); FETCH NEXT FROM db_cursor INTO @DB; END CLOSE db_cursor; DEALLOCATE db_cursor; -------------------------------------------------------------------- -- Output login scripts through new sp_help_revlogin -------------------------------------------------------------------- PRINT '------------------------------------------------------------'; PRINT '-- LOGIN MIGRATION SCRIPT FOR DATABASES: ' + @DatabaseList; PRINT '-- Run the output below on TARGET server'; PRINT '------------------------------------------------------------'; PRINT ''; DECLARE @Login sysname; DECLARE login_cursor CURSOR FOR SELECT LoginName FROM #Users ORDER BY LoginName; OPEN login_cursor; FETCH NEXT FROM login_cursor INTO @Login; WHILE @@FETCH_STATUS = 0 BEGIN PRINT '/* ===== LOGIN: ' + @Login + ' ===== */'; EXEC sp_help_revlogin @Login; PRINT ''; FETCH NEXT FROM login_cursor INTO @Login; END CLOSE login_cursor; DEALLOCATE login_cursor; END GO ``` 2. Pastikan Stored Procedure berhasil ter generate. Lihat pada database master -> Programmability -> Stored Procedure. Jika belum muncul coba refresh database. ![](https://git.gisportal.id/attachments/ab0f2869-7a8a-4db9-b956-3255c63919f9) 3. Jika sudah stored procedure sudah berhasil ter generate. Jalankan stored procedure dengan menggunakan query berikut. ***run_generate_logins_for_dbs.sql*** ``` EXEC sp_generate_logins_for_dbs 'DB1,DB2' ``` > Notes : DB1, DB2 adalah nama database 4. Copy hasilnya, pindahkan pada code editor / text editor seperti vs code dan notepad++. Save jika diperlukan. ![](https://git.gisportal.id/attachments/b6b680ec-de38-4568-ac10-0380730353bf) 5. Buka koneksi ke SQL Server tujuan migrasi. 6. Migrasikan databasenya terlebih dahulu. 7. Setelah seluruh database berhasil di migrasikan, jalankan query generate user yang sudah dibuat sebelumnya. ![](http://git.gisportal.id/attachments/1cb4cef7-7710-4b14-9605-7cf668012581) 8. Jika query berhasil di jalankan, cek pada bagian user login, user akan tampil. 9. Test login dengan user yang sudah di migrasikan. 10. Jalankan query berikut untuk mapping user database dengan user login yang sudah di migrasikan : ***run_auto_map_db_users_to_logins.sql *** ``` /* ============================================================ Auto Map DB Users to Logins (strict name match) Target : SQL Server 2022 (run on TARGET server) Behavior: - Only SQL users (dp.type='S', authentication_type=1) - Only SQL logins (sp.type='S') - If login with same name exists: * If SID mismatch -> ALTER USER ... WITH LOGIN = [name] * If SID match -> no change - Report per user: FOUND / NOT FOUND (by presence of matching login) ============================================================ */ -- 1) SET YOUR DATABASE LIST HERE DECLARE @DatabaseList NVARCHAR(MAX) = N'gis,PGE_GeoDB'; -- e.g. N'Sales,HR,Inventory' -- 2) Parse DB list DECLARE @DBs TABLE (DBName sysname PRIMARY KEY); INSERT INTO @DBs(DBName) SELECT LTRIM(RTRIM(value)) FROM string_split(@DatabaseList, ','); -- 3) Report table IF OBJECT_ID('tempdb..#Report') IS NOT NULL DROP TABLE #Report; CREATE TABLE #Report ( db_name sysname, user_name sysname, status varchar(16) -- FOUND / NOT FOUND ); -- 4) Loop each DB and process DECLARE @DB sysname, @sql nvarchar(max); DECLARE c CURSOR FOR SELECT DBName FROM @DBs; OPEN c; FETCH NEXT FROM c INTO @DB; WHILE @@FETCH_STATUS = 0 BEGIN IF DB_ID(@DB) IS NULL BEGIN PRINT CONCAT('WARNING: Database not found: ', @DB); FETCH NEXT FROM c INTO @DB; CONTINUE; END -- Dynamic SQL per database SET @sql = N' SET NOCOUNT ON; -- Collect users & matching logins by SAME NAME IF OBJECT_ID(''tempdb..#U'') IS NOT NULL DROP TABLE #U; CREATE TABLE #U ( user_name sysname PRIMARY KEY, has_login bit NOT NULL, sid_mismatch bit NOT NULL ); ;WITH dbusers AS ( SELECT dp.name, dp.sid, dp.type, dp.authentication_type FROM ' + QUOTENAME(@DB) + N'.sys.database_principals AS dp WHERE dp.type = ''S'' -- SQL_USER AND dp.authentication_type = 1 -- INSTANCE (server) auth AND dp.name NOT IN (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'') ) INSERT INTO #U(user_name, has_login, sid_mismatch) SELECT du.name AS user_name, CASE WHEN sp.principal_id IS NOT NULL THEN 1 ELSE 0 END AS has_login, CASE WHEN sp.principal_id IS NULL THEN 0 WHEN du.sid IS NULL OR sp.sid IS NULL THEN 0 WHEN du.sid <> sp.sid THEN 1 ELSE 0 END AS sid_mismatch FROM dbusers du LEFT JOIN master.sys.server_principals sp ON sp.name = du.name AND sp.type = ''S'' -- SQL login only (skip Windows/Cert/Asym) ; -- Build ALTER statements for SID mismatch (strict name match) DECLARE @alter nvarchar(max) = N''''; SELECT @alter = STRING_AGG( N''USE ' + QUOTENAME(@DB) + N'; ALTER USER '' + QUOTENAME(user_name) + N'' WITH LOGIN = '' + QUOTENAME(user_name) + N'';'', CHAR(10) ) FROM #U WHERE has_login = 1 AND sid_mismatch = 1; IF @alter IS NOT NULL AND LEN(@alter) > 0 BEGIN PRINT N''-- Applying SID remap (ALTER USER ... WITH LOGIN = <same name>) in ' + QUOTENAME(@DB) + N'...''; EXEC(@alter); END -- Insert report rows: FOUND (login exists by same name), NOT FOUND otherwise INSERT INTO #Report(db_name, user_name, status) SELECT N''' + REPLACE(@DB,'''','''''') + N''' AS db_name, user_name, CASE WHEN has_login = 1 THEN ''FOUND'' ELSE ''NOT FOUND'' END AS status FROM #U; DROP TABLE #U; '; EXEC(@sql); FETCH NEXT FROM c INTO @DB; END CLOSE c; DEALLOCATE c; -- 5) Final report SELECT db_name, user_name, status FROM #Report ORDER BY db_name, user_name; ``` 11. Jika hasil FOUND maka user berhasil ditemukan di user logins dan berhasil dimappingkan. ![](http://git.gisportal.id/attachments/8e8af201-384d-421d-988c-346fb995a218)

01.png

74 KiB

02.png

44 KiB

04.png

38 KiB

03.JPG

263 KiB