Бөлісу құралы:

Диагностика потерянных пользователей (SQL Server)

Применимо:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsАналитическая платформа (PDW)

Пользователи становятся "сиротами" в SQL Server, когда пользователь базы данных основан на имени входа в базе данных master, но имя входа больше не существует в master. Это может произойти при удалении имени входа или при перемещении базы данных на другой сервер, на котором имя входа не существует. В этой статье описывается, как найти осиротевших пользователей и сопоставить их с учетными записями.

Примечание.

Чтобы уменьшить вероятность потери пользователями доступа, используйте пользователей, привязанных к базе данных, для баз данных, которые могут быть перемещены. Дополнительные сведения см. в статье "Создание переносимой базы данных с помощью содержащихся баз данных".

Внимание

Хранимая sp_change_users_login процедура ранее использовалась для устранения осиротевших пользователей, но теперь устарела. Используйте ALTER USER ... WITH LOGIN вместо этого, как описано в разделе "Разрешение потерянных пользователей ". Дополнительные сведения см. в разделе sp_change_users_login (Transact-SQL).

Общие сведения

Для подключений к базе данных на экземпляре SQL Server, использующих субъект безопасности (удостоверение пользователя базы данных), основанного на имени входа, субъект должен иметь допустимое имя входа в master базе данных. Этот логин используется в процессе аутентификации, который проверяет идентичность субъекта и определяет, разрешено ли подключение субъекта к экземпляру SQL Server. Имена входа SQL Server на экземпляре сервера отображаются в представлении каталога sys.server_principals и представлении совместимости sys.sql_logins .

Логины SQL Server получают доступ к отдельным базам данных в качестве "пользователя базы данных", который сопоставлен с логином SQL Server. Есть три исключения из этого правила:

  • Пользователи содержащейся базы данных.

    Пользователи автономной базы данных проходят проверку подлинности на уровне пользовательской базы данных и не связаны с именами входа. Эта модель рекомендуется, так как базы данных более переносимы и пользователи автономной базы данных не могут стать потерянными. Однако их необходимо повторно создать для каждой базы данных. Эта модель может быть непрактичной в среде с множеством баз данных.

  • Гостевая учетная запись

    При включении в базе данных эта учетная запись разрешает логины SQL Server, которые не сопоставлены пользователю базы данных, для доступа к базе данных в качестве гостевого пользователя. По умолчанию учетная запись гостя отключена.

  • Членство в группах Microsoft Windows

    Имя входа SQL Server, созданное пользователем Windows, может получить доступ к базе данных, если пользователь Windows является членом группы Windows, которая также является пользователем в базе данных.

Сведения о сопоставлении имени входа SQL Server с пользователем базы данных хранятся в базе данных. Он содержит имя пользователя базы данных и идентификатор безопасности (SID) соответствующего имени входа SQL Server. Для авторизации в базе данных используются разрешения, установленные для этого пользователя базы данных.

Пользователь базы данных (на основе имени входа), для которого соответствующее имя входа SQL Server не определено или неправильно определено на экземпляре сервера, не может войти в экземпляр. Такой пользователь является потерянным пользователем базы данных на этом экземпляре сервера. Ошибка может произойти, если пользователь базы данных сопоставлен с именем входа SID , который отсутствует в master базе данных. Пользователь базы данных может стать потерянным после восстановления базы данных или подключения к другому экземпляру SQL Server, где имя входа никогда не было создано. Пользователь базы данных также может стать потерянным, если соответствующее имя входа SQL Server удалено. Даже если имя входа повторно создано, оно имеет другое SIDзначение, поэтому пользователь базы данных по-прежнему остается потерянным.

Обнаружение потерянных пользователей

Для SQL Server и PDW

Чтобы обнаружить потерянных пользователей в SQL Server на основе отсутствующих учетных записей проверки подлинности SQL Server, выполните следующую инструкцию в пользовательской базе данных:

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

В выходных данных перечислены пользователи аутентификации SQL Server и соответствующие идентификаторы SID в текущей базе данных, которые не связаны с какой-либо учётной записью SQL Server.

Для Базы данных SQL Azure и Azure Synapse Analytics

Таблица sys.server_principals недоступна в базе данных SQL или Azure Synapse Analytics. Чтобы определить потерянных пользователей в этих средах, выполните следующие действия.

  1. Подключитесь к master базе данных и выберите идентификаторы SID для имен входа с помощью следующего запроса:

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

  2. Подключитесь к пользовательской базе данных и просмотрите идентификаторы пользователей в sys.database_principals таблице с помощью следующего запроса:

    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. Сравните два списка, чтобы определить, есть ли идентификаторы безопасности пользователей в таблице пользовательской базы данных sys.database_principals , которые не соответствуют идентификаторам безопасности входа в master таблице базы данных sql_logins .

Разрешение потерянных пользователей

master В базе данных используйте инструкцию CREATE LOGIN с SID параметром повторного создания отсутствующих имен входа. SID Укажите пользователя базы данных, полученного в предыдущем разделе.

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

Чтобы сопоставить осиротевших пользователей с именем входа, уже существующим в master, выполните оператор ALTER USER в пользовательской базе данных с указанием имени входа:

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

При повторном создании отсутствующих имен входа пользователь может получить доступ к базе данных с помощью предоставленного пароля. Затем пользователь может изменить пароль учетной записи входа с помощью инструкции ALTER LOGIN :

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

Внимание

Любой пользователь может изменить свой пароль. Только учетные записи с разрешением ALTER ANY LOGIN могут изменять пароли в логинах других пользователей. Однако только члены роли sysadmin могут изменять пароли членов роли sysadmin .

Устаревшие методы

В более ранних версиях SQL Server хранимая процедура sp_change_users_login использовалась для разрешения осиротевших пользователей. Эта процедура устарела и может быть удалена в будущей версии. Вместо этого используйте ALTER USER.

В следующей таблице показан эквивалентный современный синтаксис для распространенных sp_change_users_login операций:

Устаревший синтаксис Рекомендуемая замена
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>

Дополнительные сведения см. в разделе sp_change_users_login (Transact-SQL).

Связанный контент

  • Last updated on