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

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform 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 с пользователем базы данных хранятся в базе данных. Он содержит имя пользователя базы данных и идентификатор безопасности соответствующего имени входа SQL Server. Для авторизации в базе данных используются разрешения, установленные для этого пользователя базы данных.

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

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

Для 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 и соответствующие идентификаторы безопасности в текущей базе данных, которые не связаны ни с одним именем входа SQL Server.

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

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

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

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

Устранение проблем с потерянным пользователем

Чтобы восстановить отсутствующие учетные данные, используйте инструкцию CREATE LOGIN с параметром SID в базе данных master. При этом укажите 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>';  

См. также

CREATE LOGIN (Transact-SQL)
ALTER USER (Transact-SQL)
СОЗДАНИЕ ПОЛЬЗОВАТЕЛЯ (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)
sp_change_users_login (Transact-SQL)
sp_addlogin (Transact-SQL)
sp_grantlogin (Transact-SQL)
sp_password (Transact-SQL)
sys.sysusers (Transact-SQL)
sys.sql_loginssys.syslogins (Transact-SQL)