Udostępnij przez

Rozwiązywanie problemów z osieroconymi użytkownikami (SQL Server)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Użytkownicy są osieroceni w programie SQL Server, gdy użytkownik bazy danych jest powiązany z kontem logowania w master bazie danych, ale konto logowania nie istnieje już w master programie. Może się to zdarzyć, gdy identyfikator logowania zostanie usunięty lub gdy baza danych zostanie przeniesiona na inny serwer, na którym nie istnieje identyfikator logowania. W tym artykule opisano sposób znajdowania osieroconych użytkowników i ponownego przypisywania ich do kont logowania.

Uwaga / Notatka

Zmniejsz możliwość osierocenia użytkowników, używając użytkowników baz danych z ograniczeniami dla baz danych, które mogą zostać przeniesione. Aby uzyskać więcej informacji, zobacz Uczyń swoją bazę danych przenośną, korzystając z baz danych z zawartością.

Ważne

Procedura sp_change_users_login była wcześniej używana do naprawiania osieroconych użytkowników, ale jest teraz przestarzała. Zamiast tego, użyj ALTER USER ... WITH LOGIN, jak opisano w sekcji Rozwiązywanie problemów z osieroconym użytkownikiem. Aby uzyskać więcej informacji, zobacz sp_change_users_login (Transact-SQL).

Kontekst

W przypadku połączeń z bazą danych w wystąpieniu programu SQL Server, które używają podmiotu zabezpieczeń (tożsamości użytkownika bazy danych) opartego na nazwie logowania, podmiot zabezpieczeń musi mieć prawidłową nazwę logowania w bazie danych master. To logowanie jest używane w procesie uwierzytelniania, który weryfikuje tożsamość użytkownika i określa, czy użytkownik może nawiązać połączenie z wystąpieniem programu SQL Server. Loginy SQL Server na instancji serwera są widoczne w widoku systemowym sys.server_principals oraz widoku zgodności sys.sql_logins.

Loginy SQL Server uzyskują dostęp do poszczególnych baz danych w formie "użytkownika bazy danych", który jest mapowany na login SQL Server. Istnieją trzy wyjątki od tej reguły:

  • Użytkownicy zawartej bazy danych

    Użytkownicy zawartej bazy danych uwierzytelniają się na poziomie bazy danych użytkownika i nie są powiązani z identyfikatorami logowania. Ten model jest zalecany, ponieważ bazy danych są bardziej przenośne, a użytkownicy zawartych baz danych nie mogą stać się osieroceni. Należy jednak utworzyć je ponownie dla każdej bazy danych. Ten model może być niepraktyczny w środowisku z wieloma bazami danych.

  • Konto gościa

    Po włączeniu w bazie danych to konto zezwala na logowania do SQL Server, które nie są przypisane użytkownikowi bazy danych, umożliwiając im dostęp do bazy danych jako użytkownika-gościa. Konto gościa jest domyślnie wyłączone.

  • Członkostwa w grupach systemu Microsoft Windows

    Identyfikator logowania programu SQL Server utworzony na podstawie użytkownika systemu Windows może uzyskać dostęp do bazy danych, jeśli użytkownik systemu Windows jest członkiem grupy systemu Windows, która jest również użytkownikiem w bazie danych.

Informacje o mapowaniu loginu programu SQL Server na użytkownika bazy danych są przechowywane w bazie danych. Zawiera ona nazwę użytkownika bazy danych i identyfikator zabezpieczeń (SID) odpowiedniego identyfikatora logowania programu SQL Server. Uprawnienia tego użytkownika bazy danych są stosowane do autoryzacji w bazie danych.

Użytkownik bazy danych (na podstawie logowania), dla którego odpowiednie logowanie programu SQL Server są niezdefiniowane lub niepoprawnie zdefiniowane na wystąpieniu serwera, nie może zalogować się do tego wystąpienia. Taki użytkownik jest osieroconym użytkownikiem bazy danych na tym wystąpieniu serwera. Zjawisko osierocenia może wystąpić, jeśli użytkownik bazy danych jest mapowany na identyfikator logowania SID, który nie znajduje się w bazie danych master. Użytkownik bazy danych może zostać osierocony po przywróceniu lub podłączeniu bazy danych do innego wystąpienia programu SQL Server, w którym logowanie nigdy nie zostało utworzone. Użytkownik bazy danych może również zostać osierocony, jeśli odpowiednie konto logowania do SQL Server zostanie usunięte. Nawet jeśli identyfikator logowania zostanie ponownie utworzony, ma inny SIDelement, więc użytkownik bazy danych jest nadal osierocony.

Wykrywanie osieroconych użytkowników

W przypadku programu SQL Server i pdW

Aby wykryć osieroconych użytkowników w SQL Server na podstawie brakujących loginów uwierzytelniania SQL Server, uruchom następujące polecenie w bazie danych użytkownika:

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

Dane wyjściowe zawierają listę użytkowników uwierzytelniania programu SQL Server i odpowiednich identyfikatorów SID w bieżącej bazie danych, które nie są połączone z żadnym logowaniem do programu SQL Server.

W przypadku usług Azure SQL Database i Azure Synapse Analytics

Tabela sys.server_principals nie jest dostępna w usłudze SQL Database ani w usłudze Azure Synapse Analytics. Aby zidentyfikować osieroconych użytkowników w tych środowiskach, wykonaj poniższe kroki:

  1. Połącz się z bazą master danych i wybierz identyfikatory SID dla identyfikatorów logowania przy użyciu następującego zapytania:

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

  2. Połącz się z bazą danych użytkowników i przejrzyj identyfikatory SID użytkowników w sys.database_principals tabeli przy użyciu następującego zapytania:

    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. Porównaj te dwie listy, aby określić, czy w tabeli bazy danych sys.database_principals użytkowników istnieją identyfikatory SID użytkownika, które nie są zgodne z identyfikatorami SID logowania w master tabeli bazy danych sql_logins .

Rozwiązywanie problemu z osieroconym użytkownikiem

W bazie danych użyj instrukcji masterCREATE LOGIN z SID opcją ponownego utworzenia brakującego identyfikatora logowania. SID Podaj użytkownika bazy danych uzyskanego w poprzedniej sekcji.

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

Aby zamapować osieroconego użytkownika na login, który już istnieje w master, uruchom instrukcję ALTER USER w bazie danych użytkowników, określając nazwę logowania:

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

Po ponownym utworzeniu brakującego identyfikatora logowania użytkownik może uzyskać dostęp do bazy danych przy użyciu podanego hasła. Użytkownik może następnie zmienić hasło konta logowania przy użyciu instrukcji ALTER LOGIN :

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

Ważne

Każde konto może zmienić swoje własne hasło. Tylko identyfikatory logowania z ALTER ANY LOGIN uprawnieniami mogą zmienić hasło logowania innego użytkownika. Jednak tylko członkowie roli sysadmin mogą modyfikować hasła członków roli administratora systemu .

Przestarzałe metody

We wcześniejszych wersjach programu SQL Server używano procedury składowanej sp_change_users_login do rozwiązywania problemu z osieroconymi użytkownikami. Ta procedura jest przestarzała i może zostać usunięta w przyszłej wersji. Użyj ALTER USER zamiast tego.

W poniższej tabeli przedstawiono równoważną nowoczesną składnię typowych sp_change_users_login operacji:

Składnia przestarzała Zalecane zastąpienie
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>

Aby uzyskać więcej informacji, zobacz sp_change_users_login (Transact-SQL).

Treści powiązane

  • Last updated on