Memecahkan masalah pengguna tanpa infan (SQL Server)

Artikel
06/02/2023

Berlaku untuk: SQL Server 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.

Background

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.

SQL Server login 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 disarankan 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 SQL Server login 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 SQL Server masuk 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 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, itu akan memiliki SID yang berbeda, sehingga pengguna database masih akan tanpa sumber.

Mendeteksi Pengguna Yatim Piatu

Untuk SQL Server dan PDW

Untuk mendeteksi pengguna yatim piatu di SQL Server berdasarkan SQL Server login autentikasi 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 analitik SQL Database dan Azure Synapse

Tabel sys.server_principals tidak tersedia di SQL Database atau Azure Synapse Analytics. Identifikasi pengguna yatim piatu 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 dua 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

Di 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, yang 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 masuk 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 (T-SQL)
sys.server_principals (T-SQL)
sp_change_users_login (T-SQL)
sp_addlogin (T-SQL)
sp_grantlogin (T-SQL)
sp_password (T-SQL)
sys.sysusers (Transact-SQL)
sys.sql_logins sys.syslogins (Transact-SQL)