Проблема: Я нахожусь в процессе переноса базы данных и вижу много логинов на SQL Server и несколько пользователей внутри базы данных. Очевидно, что я не хочу пропустить тех, которые относятся к базе данных. Однако я не хочу переносить логины, которые не используются базой данных. Как я могу это сделать быстро?
Решение: Сначала вам понадобится sp_help_revlogin, который предоставляется в статье Microsoft KB 918992. Эта хранимая процедура извлекает ключевую информацию, включая зашифрованный пароль, если вы работаете с логином на основе SQL Server. При переносе базы данных у нас будет несколько случаев, с которыми нужно будет справиться:
- Пользователи, сопоставленные с пользователем Windows.
- Пользователи, сопоставленные с группами Windows.
- Пользователи, сопоставленные с логинами SQL Server.
- Пользователи, сопоставленные с логином, но имена не совпадают.
- Пользователи, ранее сопоставленные с логином, но логин был удален на уровне сервера.
- Пользователи, не сопоставленные с логинами SQL Server вообще.
Последние два случая заставят нас предупредить. Мы к этому скоро перейдем, когда составим скрипт миграции. Сначала давайте настроим пример базы данных со всеми этими случаями.
Пример базы данных SQL Server
Сначала давайте настроим базу данных:
USE master;
GO
-- Удалить базу данных, если она уже существует
IF EXISTS(SELECT [name] FROM sys.databases WHERE [name] = 'TestLoginMigration')
DROP DATABASE TestLoginMigration;
GO
-- Создать нашу тестовую базу данных
CREATE DATABASE TestLoginMigration;
GO
Затем мы создадим необходимые логины. Обратите внимание, что у нас есть смесь логинов на основе Windows и SQL Server.
-- Убедитесь, что у нас есть хотя бы один логин Windows для переноса
IF NOT EXISTS(SELECT [name] FROM sys.server_principals
WHERE [name] = 'NT Authority\Authenticated Users')
CREATE LOGIN [NT Authority\Authenticated Users] FROM WINDOWS;
GO
-- Мы намеренно создадим ситуацию, когда в БД есть пользователь,
-- который ранее был сопоставлен с логином, но логин не существует, орфанный логин
IF EXISTS (SELECT [name] FROM sys.sql_logins WHERE [name] = 'LoginToBeDropped')
DROP LOGIN [LoginToBeDropped];
GO
CREATE LOGIN [LoginToBeDropped] WITH PASSWORD = 'SomeComplexPassword0987654321!';
GO
-- Теперь создадим логин, который мы собираемся сохранить
IF EXISTS (SELECT [name] FROM sys.sql_logins WHERE [name] = 'LoginToTransfer')
DROP LOGIN [LoginToBeTransferred];
GO
CREATE LOGIN [LoginToBeTransferred] WITH PASSWORD = 'SomeComplexPassword0987654321!';
GO
-- Наконец, логин, имя пользователя которого не совпадает
IF EXISTS (SELECT [name] FROM sys.sql_logins WHERE [name] = 'MismatchedName')
DROP LOGIN [MisMatchedName];
GO
CREATE LOGIN [MisMatchedName] WITH PASSWORD = 'SomeComplexPassword0987654321!';
GO
Наконец, мы сопоставим логины с пользователями на уровне базы данных. Обратите внимание, что как только мы это сделаем, мы намеренно создадим орфанный логин.
-- Добавить логины в базу данных
USE TestLoginMigration;
GO
CREATE USER [NT Authority\Authenticated Users];
GO
CREATE USER [LoginToBeDropped];
GO
CREATE USER [LoginToBeTransferred];
GO
-- Теперь для несовпадающего логина / имени пользователя
CREATE USER [NotTheLoginName] FROM LOGIN [MisMatchedName];
GO
-- Создать исключение, пользователя, который существовал только на уровне БД.
CREATE USER [UserWithoutLogin] WITHOUT LOGIN;
GO
-- Создать орфанный логин
USE master;
GO
DROP LOGIN [LoginToBeDropped];
GO
С нашей настроенной примерной базой данных давайте рассмотрим наши крайние случаи, пользователей без логинов и пользователей, у которых не совпадает имя логина. Чтобы сделать это из SSMS, разверните дерево базы данных, с которой вы работаете, и перейдите в Security > Users. Щелкните правой кнопкой мыши на одном из пользователей и выберите свойства.
Сначала давайте рассмотрим случай, когда пользователь не сопоставлен с логином. Если мы щелкнем правой кнопкой мыши на LoginToBeDropped и выберем свойства, обратите внимание, как SSMS показывает это на странице “Общие” – “SQL-пользователь без логина”. Сравните это с логином, который сопоставлен правильно. Если мы щелкнем правой кнопкой мыши на пользователе LoginToBeTransferred и выберем свойства, мы увидим это как “SQL-пользователь с логином”. Наконец, рассмотрим случай несовпадения имен. Здесь мы выбираем пользователя NotTheLoginName. Обратите внимание, что логин включен, и мы ясно видим несоответствие между пользователем и логином.
Примечание: Хотя такое несоответствие, очевидно, разрешено SQL Server, это нехорошая идея. Существует общий принцип, называемый принципом KISS. KISS означает “Делайте это просто, глупцы!” Акроним и принцип были разработаны десятилетия назад ВМС США. Он указывает на то, что когда мы делаем вещи сложнее, чем они должны быть, мы просим неприятностей. Так и случается, когда у вас есть несоответствие между именем пользователя и логином. В какой-то момент это приведет к путанице. Поэтому старайтесь избегать этого всякий раз, когда это возможно.
Сообщение о проблемах сопоставления
С нашей настроенной примерной базой данных давайте сообщим о случаях, когда пользователь не сопоставлен с логином. Если приложение работает должным образом, то эти пользователи, о которых сообщается, вероятно, могут обойтись без логинов. Однако, если они необходимы, мы предоставляем пользователей, которые не сопоставлены правильно:
-- Выполнить с результатами в текстовом режиме
-- Предупреждать о пользователях без сопоставления с логинами на уровне сервера
-- Исключая роли и "системных" пользователей
PRINT 'Следующие пользователи не сопоставлены с логинами на уровне сервера.';
PRINT ' ';
SELECT U.name, U.sid, U.create_date, U.type
FROM sys.database_principals AS U
LEFT JOIN sys.server_principals AS L
ON U.sid = L.sid
WHERE L.sid IS NULL
AND U.type IN ('S', 'U', 'G')
AND U.name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')
ORDER BY U.name;
И, если у вас есть результаты в режиме текста, вы должны увидеть нечто подобное:
Извлечение соответствующих логинов для базы данных
Теперь давайте извлечем логины, которые сопоставлены с пользователями в базе данных. Даже в случае несоответствия имен, это нормально, потому что сопоставление выполняется по sid (идентификатору безопасности). Мы будем использовать sp_help_revlogin для извлечения информации о логине и генерации оператора CREATE LOGIN.
-- Сгенерировать информацию о логине для миграции
PRINT ' ';
PRINT 'Логины для миграции:';
PRINT ' ';
DECLARE cursLogins CURSOR FAST_FORWARD
FOR
SELECT L.[name]
FROM sys.database_principals AS U
JOIN sys.server_principals AS L
ON U.sid = L.sid
WHERE U.type IN ('S', 'U', 'G')
AND NOT U.name = 'dbo'
ORDER BY L.name;
GO
DECLARE @login sysname;
DECLARE @SQL NVARCHAR(MAX);
OPEN cursLogins;
FETCH NEXT FROM cursLogins INTO @login;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL = 'EXEC master..sp_help_revlogin ''' + @login + ''';';
EXEC (@SQL);
FETCH NEXT FROM cursLogins INTO @login;
END;
CLOSE cursLogins;
DEALLOCATE cursLogins;
И вот как будет выглядеть вывод. Теперь у вас есть все необходимое для выполнения миграции.