Memecahkan masalah pengguna tanpa kepemilikan (SQL Server)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Sistem Analitik Platform (PDW)

Pengguna menjadi terisolasi di SQL Server ketika pengguna database didasarkan pada login di master database tetapi login tersebut sudah tidak ada lagi di master. Ini dapat terjadi ketika login dihapus atau ketika database dipindahkan ke server lain di mana login tidak ada. Artikel ini menjelaskan cara menemukan pengguna yatim piatu dan memetakan ulang mereka untuk masuk.

Catatan

Kurangi kemungkinan pengguna yatim piatu dengan menggunakan pengguna database mandiri untuk database yang mungkin dipindahkan. Untuk informasi selengkapnya, lihat Membuat database Anda portabel dengan menggunakan database mandiri.

Penting

Prosedur sp_change_users_login tersimpan sebelumnya digunakan untuk memperbaiki pengguna yatim piatu tetapi sekarang tidak digunakan lagi. Gunakan ALTER USER ... WITH LOGIN sebagai gantinya, sesuai penjelasan di bagian Mengatasi pengguna yatim. Untuk informasi selengkapnya, lihat sp_change_users_login (Transact-SQL).

Latar belakang

Untuk koneksi ke database pada instans SQL Server yang menggunakan prinsip keamanan (identitas pengguna database) berdasarkan login, prinsipal harus memiliki login yang valid dalam master database. 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 dapat mengakses setiap basis data sebagai "pengguna basis data" yang dipetakan ke login SQL Server. Ada tiga pengecualian untuk aturan ini:

Pengguna database terkontrol

Pengguna database mandiri mengautentikasi di tingkat database pengguna dan tidak terkait dengan login. Model ini direkomendasikan karena database lebih portabel, dan pengguna database yang terkandung tidak dapat menjadi yatim piatu. Namun, mereka harus dibuat ulang untuk setiap database. Model ini mungkin tidak praktis di lingkungan yang memiliki banyak database.
Akun tamu

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

Login SQL Server yang dibuat dari pengguna Windows dapat mengakses 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 pengidentifikasi keamanan (SID) dari login SQL Server yang sesuai. Izin pengguna database ini diterapkan untuk otorisasi dalam database.

Pengguna database (berdasarkan login) yang login SQL Server terkait tidak ditentukan atau salah didefinisikan pada instans server tidak dapat masuk ke instans. Pengguna seperti itu adalah pengguna yatim dari database pada instans server tersebut. Yatim piatu dapat terjadi jika pengguna database dipetakan ke login SID yang tidak ada dalam master database. Pengguna database dapat menjadi yatim piatu 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, ia memiliki SID yang berbeda, sehingga pengguna database masih yatim.

Mendeteksi pengguna yatim piatu

Untuk SQL Server dan PDW

Untuk mendeteksi pengguna yatim di SQL Server berdasarkan login autentikasi SQL Server yang hilang, jalankan pernyataan berikut di basis data 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 SID terkait dalam database saat ini yang tidak ditautkan ke login SQL Server apa pun.

Untuk Azure SQL Database dan Azure Synapse Analytics

Tabel sys.server_principals tidak tersedia di SQL Database atau Azure Synapse Analytics. Untuk mengidentifikasi pengguna tanpa induk di lingkungan tersebut, ikuti langkah-langkah berikut:

Sambungkan ke master database dan pilih SID untuk masuk dengan menggunakan 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 master tabel database sql_logins .

Mengatasi pengguna yatim piatu

master Dalam database, gunakan pernyataan CREATE LOGIN dengan SID opsi untuk membuat ulang login yang hilang. Berikan SID dari pengguna database yang Anda peroleh di bagian sebelumnya.

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

Untuk memetakan pengguna yatim piatu ke login yang sudah ada di master, jalankan pernyataan ALTER USER di database pengguna, tentukan nama login:

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

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

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 .

Metode yang tidak digunakan lagi

Dalam versi SQL Server yang lebih lama, prosedur tersimpan sp_change_users_login digunakan untuk mengatasi pengguna yatim piatu. Prosedur ini tidak digunakan lagi dan mungkin dihapus dalam versi mendatang. Gunakan ALTER USER sebagai gantinya.

Tabel berikut ini memperlihatkan sintaks modern yang setara untuk operasi umum sp_change_users_login :

Sintaksis yang tidak digunakan lagi	Penggantian yang direkomendasikan
`EXEC sp_change_users_login 'Report'`	`SELECT dp.name FROM sys.database_principals dp LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid WHERE sp.sid IS NULL AND dp.authentication_type_desc = 'INSTANCE'`
`EXEC sp_change_users_login 'Auto_Fix', '<user>'`	`ALTER USER <user> WITH LOGIN = <user>`
`EXEC sp_change_users_login 'Update_One', '<user>', '<login>'`	`ALTER USER <user> WITH LOGIN = <login>`

Untuk informasi selengkapnya, lihat sp_change_users_login (Transact-SQL).

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-02-26