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

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр 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 с пользователем базы данных хранятся в базе данных. Эти сведения включают в себя имя пользователя базы данных и идентификатор безопасности соответствующего имени входа 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)
CREATE USER (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)