Memecahkan masalah pengguna tanpa sumber (SQL Server)

Artikel
07/20/2022
3 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Pengguna tanpa induk di SQL Server terjadi ketika pengguna database didasarkan pada login di database master , tetapi login tidak lagi ada di master. Ini dapat terjadi ketika login dihapus, atau ketika database dipindahkan ke server lain di mana login tidak ada. Topik ini menjelaskan cara menemukan pengguna yatim piatu, dan memetakan ulang mereka ke login.

Catatan

Kurangi kemungkinan pengguna tanpa sumber dengan menggunakan pengguna database mandiri untuk database yang mungkin dipindahkan. Untuk informasi selengkapnya, lihat Pengguna Database Mandiri - Membuat Database Anda Portabel.

Latar belakang

Untuk menyambungkan ke database pada instans SQL Server menggunakan prinsip keamanan (identitas pengguna database) berdasarkan login, prinsipal harus memiliki login yang valid di database master . Login ini digunakan dalam proses autentikasi yang memverifikasi identitas utama dan menentukan apakah prinsipal diizinkan untuk terhubung ke instans SQL Server. Login SQL Server pada instans server terlihat dalam tampilan katalog sys.server_principals dan tampilan kompatibilitas sys.sql_logins .

Login SQL Server mengakses database individual sebagai "pengguna database" yang dipetakan ke login SQL Server. Ada tiga pengecualian untuk aturan ini:

Pengguna database mandiri

Pengguna database mandiri mengautentikasi di tingkat database pengguna dan tidak terkait dengan login. Ini direkomendasikan karena database lebih portabel dan berisi pengguna database tidak dapat menjadi tanpa sumber. Namun mereka harus dibuat ulang untuk setiap database. Ini mungkin tidak praktis di lingkungan dengan banyak database.
Akun tamu .

Saat diaktifkan dalam database, akun ini mengizinkan login SQL Server yang tidak dipetakan ke pengguna database untuk memasukkan database sebagai pengguna tamu . Akun tamu dinonaktifkan secara default.
Keanggotaan grup Microsoft Windows.

Login SQL Server yang dibuat dari pengguna Windows dapat memasukkan database jika pengguna Windows adalah anggota grup Windows yang juga merupakan pengguna dalam database.

Informasi tentang pemetaan login SQL Server ke pengguna database disimpan dalam database. Ini termasuk nama pengguna database dan SID login SQL Server yang sesuai. Izin pengguna database ini diterapkan untuk otorisasi dalam database.

Pengguna database (berdasarkan login) yang login SQL Server terkait tidak terdefinisi atau salah didefinisikan pada instans server tidak dapat masuk ke instans. Pengguna seperti itu dikatakan sebagai pengguna tanpa sumber dari database pada instans server tersebut. Orphaning dapat terjadi jika pengguna database dipetakan ke SID login yang tidak ada dalam master instans. Pengguna database dapat menjadi tanpa sumber setelah database dipulihkan atau dilampirkan ke instans SQL Server yang berbeda di mana login tidak pernah dibuat. Pengguna database juga dapat menjadi yatim piatu jika login SQL Server yang sesuai dihilangkan. Bahkan jika login dibuat ulang, proses masuk akan memiliki SID yang berbeda, sehingga pengguna database akan tetap tanpa sumber.

Mendeteksi Pengguna Tanpa Sumber

Untuk SQL Server dan PDW

Untuk mendeteksi pengguna tanpa sumber di SQL Server berdasarkan login autentikasi SQL Server yang hilang, jalankan pernyataan berikut dalam database pengguna:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  
FROM sys.database_principals AS dp  
LEFT JOIN sys.server_principals AS sp  
    ON dp.sid = sp.sid  
WHERE sp.sid IS NULL  
    AND dp.authentication_type_desc = 'INSTANCE';

Output mencantumkan pengguna autentikasi SQL Server dan pengidentifikasi keamanan (SID) terkait dalam database saat ini yang tidak ditautkan ke login SQL Server apa pun.

Untuk SQL Database dan Azure Synapse Analytics

Tabel sys.server_principals tidak tersedia di SQL Database atau Azure Synapse Analytics. Identifikasi pengguna tanpa sumber di lingkungan tersebut dengan langkah-langkah berikut:

Sambungkan ke master database dan pilih SID untuk masuk dengan kueri berikut:
```
SELECT sid 
FROM sys.sql_logins 
WHERE type = 'S'; 
```

Sambungkan ke database pengguna dan tinjau SID pengguna dalam sys.database_principals tabel, dengan menggunakan kueri berikut:

SELECT name, sid, principal_id
FROM sys.database_principals 
WHERE type = 'S' 
  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
  AND authentication_type_desc = 'INSTANCE';

Bandingkan kedua daftar untuk menentukan apakah ada SID pengguna dalam tabel database sys.database_principals pengguna yang tidak cocok dengan SID masuk dalam tabel database sql_logins master.

Mengatasi Pengguna Tanpa Sumber

Dalam database master, gunakan pernyataan CREATE LOGIN dengan opsi SID untuk membuat ulang login yang hilang, menyediakan SID pengguna database yang diperoleh di bagian sebelumnya:

CREATE LOGIN <login_name>   
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;

Untuk memetakan pengguna tanpa induk ke login yang sudah ada di master, jalankan pernyataan ALTER USER dalam database pengguna, menentukan nama login.

ALTER USER <user_name> WITH Login = <login_name>;

Saat Anda membuat ulang login yang hilang, pengguna dapat mengakses database menggunakan kata sandi yang disediakan. Kemudian pengguna dapat mengubah kata sandi akun login dengan menggunakan pernyataan ALTER LOGIN.

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

Penting

Setiap login dapat mengubah kata sandinya sendiri. Hanya login dengan izin yang ALTER ANY LOGIN dapat mengubah kata sandi login pengguna lain. Namun, hanya anggota peran sysadmin yang dapat memodifikasi kata sandi anggota peran sysadmin .

Lihat juga

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
CREATE USER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_logins sys.syslogins (Transact-SQL)