.::: Backup All User, Specify User , Permission, Roles, Server Roles in SQL Server :::.

 
Untuk melakukan backup user dan privilege di SQL Server, Anda bisa menggunakan perintah Transact-SQL (T-SQL) untuk mengekstrak informasi tentang pengguna (user) dan hak akses (privilege). Berikut langkah-langkah yang dapat dilakukan:

1. Backup User Login dan Mapping ke Database

Gunakan skrip di bawah ini untuk mencadangkan login pengguna dan mappingnya ke database.
Output dari skrip ini akan memberikan Anda perintah untuk membuat ulang login yang sudah ada. Perhatikan bahwa password tidak dapat diekstrak, jadi Anda perlu memasukkannya secara manual saat melakukan restore.

-- Backup logins
SELECT @@servername as ServerName,'CREATE LOGIN [' + l.name + '] '
    + CASE
        WHEN l.type_desc = 'SQL_LOGIN' THEN 'WITH PASSWORD = ''<password>'', '
        WHEN l.type_desc = 'WINDOWS_LOGIN' THEN 'FROM WINDOWS, '
        ELSE ''
      END
    + ' DEFAULT_DATABASE=[' + l.default_database_name + ']'
    + CASE
        WHEN l.is_disabled = 1 THEN ', DISABLE;'
        ELSE ';'
      END AS CreateLoginScript
FROM sys.server_principals l
WHERE l.type IN ('S', 'U');  -- SQL Logins (S) and Windows Logins (U)
 

2. Backup User di Setiap Database

Untuk mencadangkan pengguna dalam setiap database beserta peran (roles) dan hak akses, jalankan skrip berikut untuk setiap database:

Skrip di atas akan menghasilkan perintah untuk membuat ulang user di database yang bersangkutan.

USE [teguhth];  -- Ganti dengan nama database yang diinginkan

SELECT @@servername as ServerName,'CREATE USER [' + dp.name + '] FOR LOGIN [' + sp.name + '];' AS CreateUserScript
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U');  -- SQL User (S) dan Windows User (U)
 

3. Backup Permissions (Hak Akses)

Untuk mencadangkan hak akses pengguna (permission), gunakan skrip berikut untuk setiap database:

Skrip ini akan menampilkan hak akses yang diberikan kepada setiap pengguna terhadap objek dalam database (misalnya tabel, prosedur).

USE [teguhth];  -- Ganti dengan nama database yang diinginkan

SELECT @@servername as ServerName,
    dp.name AS DatabasePrincipal,
    o.name AS ObjectName,
    p.permission_name AS Permission,
    p.state_desc AS PermissionState
FROM
    sys.database_permissions p
JOIN
    sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN
    sys.objects o ON p.major_id = o.object_id
ORDER BY
    dp.name, o.name;
 

4. Backup Roles
Jika ada role tertentu yang sudah ditetapkan kepada pengguna, gunakan skrip berikut untuk mengekstrak peran pengguna:
Skrip ini akan menampilkan semua user yang menjadi anggota dari role tertentu.

USE [teguhth];  -- Ganti dengan nama database yang diinginkan

SELECT @@servername as ServerName,dp.name AS UserName,
       r.name AS RoleName
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id;
 

5. Backup Server Roles
Untuk mencadangkan peran server pada level server:
Skrip ini akan menampilkan pengguna dan role pada level server.

SELECT @@servername as ServerName,pr.name AS MemberName,
       r.name AS RoleName
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals pr ON rm.member_principal_id = pr.principal_id;
 

Restore
Setelah Anda menjalankan semua skrip di atas dan mendapatkan hasilnya, simpan output sebagai file skrip yang dapat Anda gunakan untuk me-restore user, login, dan hak akses mereka di SQL Server yang baru atau yang sama jika terjadi kegagalan.

Catatan
Saat mencadangkan, password dari SQL logins tidak bisa diekstrak karena keamanannya, jadi password perlu di-set ulang pada server tujuan.
Untuk hak akses spesifik pada level objek, pastikan Anda sudah mencadangkan semuanya, terutama untuk objek-objek penting seperti tabel atau prosedur.
Ini adalah cara yang umum digunakan untuk backup user dan privilege di SQL Server. Jika ada langkah khusus yang Anda perlukan atau lingkungan tertentu yang harus diatasi, jangan ragu untuk bertanya lebih lanjut.