Bagikan melalui

Memecahkan masalah pengguna tanpa infan (SQL Server)

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics AnalyticsPlatform System (PDW)

Pengguna yatim piatu di SQL Server ketika pengguna database didasarkan pada login dalam master database 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. 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.

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 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. 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 dikatakan sebagai pengguna tanpa intim 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, login akan memiliki yang berbeda SID, sehingga pengguna database masih akan menjadi yatim piatu.

Mendeteksi pengguna yatim piatu

Untuk SQL Server dan PDW

Untuk mendeteksi pengguna tanpa intim di SQL Server berdasarkan login autentikasi SQL Server yang hilang, jalankan pernyataan berikut di 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 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. Identifikasi pengguna tanpa intim di lingkungan tersebut dengan menyelesaikan langkah-langkah berikut:

  1. Sambungkan ke master database dan pilih SID untuk masuk dengan menggunakan kueri berikut:

    SELECT sid
FROM sys.sql_logins
WHERE type = 'S';

  2. 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';

  3. 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 tanpa intim

master Dalam database, gunakan pernyataan CREATE LOGIN dengan SID opsi untuk membuat ulang login yang hilang. SID Berikan 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 .

Konten terkait

  • Last updated on