Я переместил базу данных с sql 2000 на новый сервер 2005 года. Все было хорошо, пока я не вошел в студию Sql Server Management с одним конкретным пользователем. Похоже, я успешно вошел в систему, но когда я пытаюсь расширить базу данных, я получаю сообщение «База данных недоступна». Я могу войти в систему с проверкой подлинности Windows и с другими пользователями sql без проблем и все вижу.
Я проверил и убедился, что БД не находится в однопользовательском режиме. Я также проверил наличие потерянных логинов (exec sp_change_users_login 'report'), и ничего не было найдено. Пользователь также имеет права на базу данных.
Любая помощь в указании мне правильного направления будет принята с благодарностью.
Скорее всего, это проблема с разрешениями на новом сервере. Ваши логины SQL не переносятся автоматически, и если вы вручную создадите его на новом сервере, он получит другой SID, который не будет соответствовать тому, которому предоставлен доступ к базе данных.
Лучший способ - это используйте sp_help_revlogin (KB918992 на случай, если ссылка когда-либо прервется), чтобы сгенерировать сценарий входа в систему на старом сервере, а затем запустить сценарий на новом сервере. Эта процедура перенесет информацию об SID и пароле на новый сервер, эффективно воссоздавая логин на новом сервере. Как только это будет сделано, разрешения, настроенные в вашей базе данных, должны быть синхронизированы с логином (ами) на новом сервере.
Это не должно быть проблемой для входа в Windows, поскольку SQL получает SID из Windows, хотя вам все равно необходимо настроить вход в Windows на новом сервере. sp_help_revlogin также выполнит эту часть за вас.
Спасибо за ответы! После некоторого времени я удалил пользователя из базы данных и воссоздал его, что сработало! Я удалил пользователя и воссоздал его раньше, но раньше я делал это на уровне сервера, а не на уровне БД. Как только я удалил пользователя из БД и воссоздал его, все заработало. Спасибо еще раз за помощь!
У вас не возникнет проблем с входом в систему на основе Windows, поскольку они не связаны с базами данных по SID. Так обстоит дело с входами в систему SQL, поскольку в результате действительно происходит "осиротение". Лучше всего использовать sp_help_revlogin как адресованное (проактивно) или использовать sp_change_users_login (реактивно). Я использовал следующий сценарий, который я готовил все время, прежде чем обнаружил sp_help_revlogin много лет назад. Это все еще работает:
DECLARE @user SYSNAME
DECLARE @SQL NVARCHAR(300)
DECLARE cur_Users CURSOR FOR
SELECT name
FROM sysusers
WHERE islogin = 1
AND isntname = 0
AND NAME NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')
ORDER BY name
OPEN cur_Users
FETCH NEXT
FROM cur_Users INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'EXEC sp_change_users_login ' + '''' + 'UPDATE_ONE'
+ '''' + ', ' + '''' + @user + '''' + ', ' + '''' + @user + ''''
EXEC sp_executesql @SQL
FETCH NEXT
FROM cur_Users INTO @user
END
CLOSE cur_Users
DEALLOCATE cur_Users
Одна из вещей, которая изменилась с 2000 по 2005 год, связана с новым в 2005 году разрешением уровня базы данных под названием CONNECT. В SQL Server 2000 быть пользователем в базе данных также означало, что у вас есть доступ к базе данных. Однако в SQL Server 2005 самого по себе быть пользователем базы данных недостаточно, чтобы предоставить пользователю доступ к ней. Обычно вы этого даже не замечаете, поскольку создание пользователя в SQL Server 2005 через DDL или через графический интерфейс автоматически предоставит вашему пользователю разрешение CONNECT. Неужели вы как-то потеряли это при обновлении?
Я могу воссоздать вашу проблему, создав логин и сопоставив логин с базой данных, а затем перейдя в базу данных и выполнив REVOKE CONNECT TO имя пользователя. Когда я пытаюсь получить доступ к этой базе данных через SSMS как имя пользователя, я получаю то же сообщение об ошибке: База данных недоступна.
Если вы уверены, что пользователь не осиротел, я бы либо перешел на страницу разрешений свойств базы данных через SSMS и убедился, что у вашего проблемного пользователя есть Grant для разрешения CONNECT, либо я бы выполнил следующий запрос:
ВЫБЕРИТЕ pr.name, per. * FROM sys.database_principals AS pr JOIN sys.database_permissions AS per ON pr.principal_id = per.grantee_principal_id WHERE pr.name = 'ваше имя пользователя здесь' AND per.permission_name = 'CONNECT';
Если строки не возвращаются, то вашему пользователю необходимо снова добавить разрешение CONNECT, чтобы получить доступ к базе данных должным образом.
Это было обновление или вы отсоединили и снова подключили? Если вы отсоедините и переместите базу данных на другой сервер, пользователи появятся в базе данных, но они не обязательно получат ловушки безопасности. Вам может потребоваться либо сбросить их разрешения на сервере, либо удалить пользователя и воссоздать его.
Вы также можете использовать хранимую процедуру sp_change_users_login, чтобы переназначить пользователя базы данных для соответствующего имени входа на новом сервере. Это помогает без удаления пользователя базы данных. Это также сохраняет права пользователя, что очень удобно, если вы установили детальные разрешения для объектов в базе данных. (Обратите внимание, что это не работает с логинами с аутентификацией Windows, но обычно у меня нет проблем с их синхронизацией.)