Поделиться через

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

Применимо:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azureazure Synapse Analytics AnalyticsPlatform System (PDW)

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

Примечание.

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

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

Для подключений к базе данных на экземпляре 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 .

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

  • Last updated on