Как перевести базу в однопользовательский режим sql

Получение административных привилегий в Microsoft SQL Server

После смены рабочей станции начал ставить на нее Micorosft SQL Server 2008 R2 и чуть было не натолкнулся на традиционные грабли, связанные с улучшенной безопасностью в этой версии. Если в Microsoft SQL Server 2005 группа локальных администраторов по умолчанию включалась в роль sysadmin на SQL сервере, то в 2008-й в эту роль не включается никто:

В итоге, в инсталляции по умолчанию получается ситуация, в которой к инстансу не имеет административного доступа никто, то есть сделать с этим инстансом нельзя ничего кроме как периодически перезагружать его. Также такая ситуация возникает, когда тот, кто устанавливал SQL сервер, назначив себя единственным администратором, увольняется — например такая ситуация возникла нашими админами.
Данный пост показывает решение этой проблемы и предоставляет автоматизированное решение этой проблемы в виде скрипта, ровно как и рассказывает историю его написания, иллюстрируя мощь WMI, которая недопустимо замалчивается в литературе и в интернете.

Описание процедуры

1. Перезагрузить инстанс в однопользовательский режим (single user mode)
2. Добавить нужного пользователя в администраторы сервера из-под любого пользователя из группы локальных администраторов
3. Перезагрузить инстанс в нормальный режим

Разжеванное описание процедуры

Перегрузка в однопользовательский режим

1. Запускаем оснастку конфигурации SQL сервером и останавливаем нужный инстанс (в моем случае — инстанс по умолчанию):
   
2. Открываем свойства инстанса:
   
3. Переключаемся на вкладку Advanced и прокручиваем свойства к параметру Startup Parameters:
   
4. Добавляем параметр -m; (не забываем точку с запятой!). Этот параметр обозначает загрузку инстанса в однопользовательском режиме (single user mode). В этом режиме любой член группы локальных администраторов имеет привилегии системного администратора на инстансе. Также в этом режиме возможно единственное соединение с сервером, поэтому любые приложения, которые могут хотеть присоединиться к конфигурируемому инстансу, должны быть погашены. Полное описание параметров движка базы можно найти тут:
   
5. Запускаем инстанс:
   

Установка админских привилегий для пользователя

Тут есть много способов, начиная от присоединения к серверу посредством SQL Server Management Studio и использования графической оснастки для добавления нужных прав и кончая использованием osql. Мы пойдем вторым путем. Запускаем cmd.exe под пользователем из группы локальных администаторов и выполняем сдедующую команду:
osql -E -S .\InstanceName -Q "EXEC sp_addsrvrolemember ‘DOM\User’, ‘sysadmin’" , где InstanceName — имя инстанса, а DOM\User — это домен\пользователь, которому дается административный доступ к инстансу. В моем случае (с инстансом по умолчанию и для админского пользователя RU\venticello) выглядит это так:

Запуск инстанса в обычном режиме

1. Останавливаем инстанс
2. Удаляем параметр -m;
3. Запускаем инстанс

Автоматизация

Хоть процедура и не архисложная и никоим образом не каждодневная, она, если честно, немного занудная и утомительная. Одно количество скриншотов является тому подтверждением. Я же являюсь убежденным апологетом утверждения, что все, что занудно, должно делаться компьютером, а не человеком — на то их и создавали. Поэтому я взял и описал все эти шаги в виде скрипта, предлагаемого вашему вниманию. Чтобы воспользоваться скриптом, его надо запустить из-под пользователя с административными привилегиями на машине с инстансом следующим образом:
cscript /nologo acquire_admin_rights.js [<instance-name>] , где опциональный параметр instance-name обозначает инстанс, к которому надо предоставить админские права для запускающего пользователя. Если пропустить инстанс или задать имя MSSQLSERVER, доступ будет предоставлен к инстансу по умолчанию. Еще раз напоминаю, что надо удостовериться, что в течение процедуры нет никаких приложений, активно соединяющихся с этим инстансом, так как они могут перехватить единственное соединение, предоставляемое однопользовательским режимом.
В процессе работы скрипт честно рассказывает о своих деяниях, поэтому, если что-то пойдет не так, можно понять, в чем причина и в каком состоянии оставлена система:

Детали по скрипту

Когда я начал писать скрипт, у меня уже был некоторый опыт работы с конфигурацией SQL Server через WMI, но именно с параметрам командной строки запуска инстанса работать не приходилось. Именно в этом ключе я и поведу рассказ: что я знал, и как искал то, что мне нужно.

Вкратце, в контексте нашего повествования, WMI (Windows Management Instrumentation) — это сервис Windows, предоставляющий доступ к конфигурационной информации в унифицированном виде именованных классов, представленных набором свойств. Классы распиханы по пространствам имен (самое популярные из которых — это root\cimv2, в котором живет большинство классов, описывающих систему, и root\default, в котором живет класс реестра). На основании класса может существовать один или более экземпляров, обозначающих реальные описываемые объекты. Например, класс Win32_Service — это понятие службы, а каждый экземпляр — это набор свойств, соответствующий реальным службам, установленным на системе.

Microsoft SQL Server в WMI

• root\Microsoft\SqlServer\ComputerManagement — для SQL Server 2005
• root\Microsoft\SqlServer\ComputerManagement10 — для SQL Server 2008

wbemtest

wbemtest.exe — это стандартный клиент WMI (настолько стандартный, что присутствует в путях), поставляемый c WMI с первых дней появления этого сервиса аж в Windows 2000. Как следствие, интерфейс у этой утилиты суров, что, однако, не приумаляет его мощь. Выглядит он так:

Пока мы не присоединимся к нужному пространству имен, делать нам в этой утилитке особо нечего. К счастью, мы знаем нужное пространство имен: root\Microsoft\SqlServer\ComputerManagement10 :

Если с WMI все нормально (а у этого сервиса есть тенденция изредка отваливаться), то соединение будет успешным, приглашая нас к взаимодействию активными кнопками:

Ну все, теперь мы готовы копаться в пространстве имен в поисках нужных классов и свойств.

Поиск нужных свойств

Сначала смотрим, какие классы вообще существуют в этом пространстве имен. Для этого, очевидно, жмем на кнопку Enum Classes и в появившемся не совсем понятном диаложке нажимаем OK. В итоге появляется следующее окно:
.
Обычная женская интуиция подсказываем нам, что это, скорее всего, класс SqlServiceAdvancedProperty. Даблкликаем, открывая следующий диалог, показывающий свойства данного класса:

Похоже на правду. Посмотрим на экземпляры этого класса и посмотрим, есть ли там интересующие нас параметры. Для этого нажимаем кнопку Instances и получаем сие окно:

Находим объект SqlServiceAdvancedProperty.PropertyIndex=13,SqlServiceType=1,PropertyName=’STARTUPPARAMETERS’,ServiceName=’MSSQLSERVER’. Вот оно счастье!

ALTER DATABASE SET SINGLE_USER statement in SQL Server

In this article, we are going to explore the purpose of the ALTER DATABASE SET SINGLE_USER statement. The ALTER DATABASE SET SINGLE_USER is used to put the database in single-user mode. When any database is in single-user mode, the new user cannot connect to the database. However, the users that are already connected to the database do not get disconnected.

When you are overwriting a database using a backup or trying to detach any database, you might want to change the user access mode to SINGLE_USER to make sure that new users can not connect to the database. When you run the ALTER DATABASE SET SINGLE_USER statement, it does not complete because the users are still connected to it.

To override this behavior, we can use any of the following termination options.

1. WITH ROLLBACK IMMEDIATE: This option will rollback all the open transactions. This option does not wait for the transaction to complete. When you are rolling back a long-running transaction, the command takes more time to complete, so make sure you check all long-running transactions before executing the command
2. WITH NO_WAIT: This option does not rollback any transaction but, it waits till all transaction completes. It waits for a specified time, and if the transaction does not complete, the ALTER DATABASE statement fails. This approach is considered the cleanest method to change the access mode because it does not roll back any transaction

Permission

To execute the ALTER DATABASE SET SINGLE_USER statement requires ALTER DATABASE permission.

Different methods to access the mode of the database to a SINGLE_USER

We change the access mode of the database to a single user by using any of the following methods:

1. Changing the database property in SQL Server Management Studio (SSMS)
2. T-SQL ALTER DATABASE statement

Change Database Property in SQL Server Management Studio (SSMS)

To change the database mode using SSMS, open SQL Server Management Studio Connect to the database engine Expand Databases Right-click on AdventureWorks2017.

In the database properties dialog box, click on Options. Click on Restrict Access drop-down box and select SINGLE_USER. Click OK to save the configuration.

You will receive a message stating that we must close all open connections before changing the access mode. Click on OK to close all active user connections and change the access mode.

The access mode of the AdventureWorks2017 database is changed to SINGLE_USER. You can see the (Single-User) is specified with the database name in SQL Server Management studio.

T-SQL ALTER DATABASE Statement

We can use ALTER DATABASE SET SINGLE_USER statement to change the access mode of the user database to SINGLE_USER. To change the access mode of the AdventureWorks2017. Before changing the access mode of the database, we must close the open connections. To do that, we must use the ROLLBACK IMMEDIATE termination clause. The ALTER DATABASE command must be written as follows:

1.3. Редактирование параметров базы данных

Изменения в настройках базы данных происходят не часто, но они происходят. В этой главе мы научимся вносить изменения в базе данных с помощью SQL. Для изменения используется оператор ALTER DATABASE. В общем виде этот оператор можно увидеть в листинге 1.8.

Листинг 1.8. Общий вид команды ALTER DATABASE

В первой строке параметр database – это имя, базы данных, которую надо изменить. Далее построчно идут параметры или свойства базы данных, которые можно, а иногда и нужно изменить. Давайте рассматривать возможности команды с одновременным рассмотрением примеров, потому что возможностей у ALTER DATABASE столько же, сколько и у команды создания базы данных CREATE DATABASE.

1.3.1. Изменение файла

Когда данные увеличиваются или увеличивается активность изменения данных, вам может понадобиться увеличить размер файлов данных или журнала. Для увеличения размер файлов можно использовать программу управления базой данных SQL Server Enterprise Manager, которая обладает визуальным интерфейсом или воспользоваться оператором ALTER DATABASE.

Вы можете контролировать размер базы данных с помощью:

• Конфигурирования файлов базы данных и журнала на автоматическое приращение.
• Увеличивать или уменьшать текущее значение максимального размера файлов базы данных и журнала вручную.
• Вручную добавлять дополнительные файлы журнала или данных.

Вы можете установить автоматическое приращение с помощью оператора ALTER DATABASE. Использование автоматического приращения уменьшает администраторские задачи, заключенные в ручном увеличении размера базы данных, но сложнее контролировать количество используемого сервером MS SQL Server пространства.

Увеличение лучше всего контролировать самостоятельно. Да, это отнимет лишнее рабочее время и потребует некоторых усилий, но контроль никогда не бывает лишним. Спокойно спит только тот администратор, у которого все находиться под контролем.

Если вы не сконфигурировали существующие файлы на автоматическое приращение, вы все еще можете увеличить размер. Если указан ноль для значения автоматического увеличения, значит, автоматического увеличения нет.

Вы можете создать вторичные файлы базы данных, для расширения размера базы данных. Но лучше использовать дополнительные файлы базы данных, чтобы расположить файлы на отдельном физическом диске или выделить объект базы данных из общей массы.

Следующий пример увеличивает текущий размер журнала базы данных NewDB:

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

Вот тут необходимо сделать одно замечание, если для переноса данных с одного сервера на другой захотите воспользоваться функциями отключения и подключения базы данных, то не забудьте скопировать в новое место все файлы.

Допустим, что вам необходимо произвести массовую загрузку данных. База данных на данный момент занимает 10 мегабайт, а приращение, указанное в настройках в случае нехватки места составляет 1 мегабайт. Если вы будете загружать еще 10МБ данных, то во время загрузки произойдет 10 увеличений по одному мегабайту файла данных. Это отнимет лишнее время, поэтому вполне логичным будет увеличить размер файла вручную.

Для ручного увеличения размера файла до 200Мб необходимо выполнить следующую команду:

Вторая строка содержит команду MODIFY FILE, что означает изменения файла. В скобках необходимо указать имя файла, размер которого надо изменить в параметре NAME и новый размер. Новый размер обязательно должен быть больше текущего, потому что команда позволяет только увеличивать файл, но не уменьшать.

Если вы хотите узнать текущий размер файлов или не помните имена, выполните команду:

где имя — имя базы данных. Например, следующая команда отображает информацию о базе данных FlenovSQLBook, в которую входит информация и о файлах:

Состояния базы данных можно увидеть выполнив команду: EXEC sp_helpdb TestDatabase.

Помните, что таким образом вы можете только увеличивать размер файлов, но не уменьшать. Уменьшение файлов данных и журнала – это отдельная тема. Итак, если вы создали базу данных с файлом в 1 Мб, и потом увеличили ее размер до 5 мегабайт то вернуть изначальный размер командой ALTER DATABASE невозможно. Это значит, что следующий запрос вернет ошибку, если размер файла FlenovSQLBook больше 2Мб:

С помощью команды ALTER DATABASE можно изменять и размер файла журнала, только в параметре NAME нужно указать имя файла журнала, который нужно увеличить:

Для оптимальной производительности сервера и уменьшения количества увеличения файлов (это отнимает лишнее время и может затормозить работу сервера):

• Выделяйте необходимое количество начального пространство, чтобы избежать частого автоматического приращения;
• Указывайте максимальный размер файлов данных, если у вас есть несколько баз данных;
• Устанавливайте размер приращения файлов данных и журнала так, чтобы не было частого автоматического увеличения.

Когда база данных увеличивается или когда увеличивается активность изменений, вам может понадобиться расширить журнал транзакций. Внимательно следите, чтобы журнал транзакций не оказался слишком маленьким. Регулярное наблюдение за журналом поможет вам определить оптимальное увеличение журнала и не допустить его переполнения. Если ваш журнал транзакций не может быть увеличен, то SQL Server не сможет записывать транзакции и не позволить делать изменения в базе данных.

Вы можете следить за журналом транзакций с помощью SQL Server Enterprise Manager, оператора DBCC SQLPERF (LOGSPACE) (подробней о команде в разделе 4.2.5) или системный монитор Windows.

Если данные изменяются достаточно часто, то необходимо выделить максимально возможное (но разумное) пространство и следить, чтобы всегда было достаточно свободного места в журнале даже для выполнения самой массовой операции обновления данных. Если база используется редко, и изменения происходят время от незначительно, то журнал можно выделить небольшим. Помните, что журнал транзакций пополняется только во время добавления, изменения и удаления данных. Во время выборки журнал не используется.

Некоторые ситуации, которые увеличивают активность журнала транзакций:

• Загрузка информации в таблицу, которая содержит индексы. Сервер SQL записывает все вставки и изменение индексов. При загрузке таблиц без индексов, SQL Server записывает в журнал только расширение пространства.
• Транзакции, которые выполняют много изменений (INSERT, UPDATE и DELETE) в таблице с помощью одной транзакции.
• Добавление или изменение данных Image или Text.

1.3.2. Добавление/удаление файла

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

Допустим, что на вашем основном диске, где расположена база данных, заканчивается место на диске. В этом случае, если есть другие логические разделы или жесткие диски, можно добавить к серверу новый файл и расположить его на этом диске. Добавление чем-то похоже на определение файла при создании базы данных:

В данном примере изменяется уже существующая база данных, на которой мы тестировали не один пример — TestDatabase. Затем указывается ключевое слово ADD FILE, указывающее на необходимость добавить новый файл. После этого ключевого слова, в круглых скобках описываются параметры файла точно так же, как при описании файла во время создания базы данных.

В качестве расширения для файла выбрано .ndf. Такое расширение рекомендуется для всех вторичных файлов, но может быть и другим. Напоминаю, что для основного файла рекомендуется указывать расширение .mdf.

Для удаления файла из базы данных совместно с ALTER DATABASE используется ключевое слово REMOVE FILE. Если вы создали файл и поместили в него таблицу, то файл уже нельзя удалить, пока он не станет пустым, то есть, пока из файла не будут удалены все таблицы.

Нам в данной книге лишний файл не нужен, поэтому давайте удалим то, что создали. Для этого нужно указать оператор REMOVE FILE и имя удаляемого файла:

1.3.3. Добавление/удаление файловых групп

Для добавления файловой группы используется оператор ADD FILEGROUP. Например, следующий сценарий добавляет группу с именем fgNewGroup:

Теперь посмотрим, как можно добавить файл в эту группу:

Принцип такой же, как и у добавления файла, но после скобок указывается TO FILEGROUP и имя группы, в которую добавляется файл.

Файловую группу нельзя удалять, если в ней есть файл. Сначала необходимо удалить этот файл, а потом уже удалять группу. Для удаления группы используется REMOVE FILEGROUP, например:

1.3.4. Переименование базы данных

Иногда бывает необходимость переименовать базу данных. В моей практике это очень редко приходилось делать, но все же. Переименовать можно с помощью оператора MODIFY NAME. Например, следующий сценарий изменяет имя базы данных TestDatabase на MyDatabase:

При этом вы не должны быть подключены к этой базе данных, лучше всего, если подключение будет к базе данных master. Если к базе данных, которую необходимо переименовать будет подключен хоть один пользователь, то переименование не сможет быть выполнено.

Если вы попробовали выполнить этот сценарий, то верните ей старое имя TestDatabase, потому что в дальнейшем при тестировании сценариев мы будем ссылаться на него.

1.3.5. Изменение свойств базы данных

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

Для изменения свойства используется оператор SET. Команда будет выглядеть следующим образом:

После ALTER DATABASE указывается имя базы данных, свойства которой нужно изменить, а после оператора SET нужно указать имя свойства.

Давайте посмотрим имена свойств которые нужно подставить вместо параметра имя_свойства:

• SINGLE_USER – перевести базу данных в однопользовательский режим. Только один пользователь сможет работать с базой;
• RESTRICTED_USER – к базе данных разрешено подключаться только пользователям, которые принадлежат роли db_owner, dbcreator или sysadmin;
• MULTI_USER – нормальный многопользовательский режим, при котором действуют все права (используется по умолчанию);
• OFFLINE – отключить базу данных, подключения будут невозможны. Команды должна выполняться, когда к базе данных нет активных подключений. Вы при этом должны быть подключены к базе данных master.
• ONLINE – вернуть базу данных в активное состояние;
• READ_ONLY — перевести базу данных в режим только для чтения, изменение данных будет невозможно;
• READ_WRITE — вернуть базе данных полный доступ на запись и чтение;
• CURSOR_CLOSE_ON_COMMIT ON – по завершении транзакции (принятии или откате) все открытые курсоры будут закрываться. Если ON заменить на OFF, то при нормальном завершении транзакции (принятии изменений) курсоры остаются открытыми. При откате все курсоры кроме INSENSITIVE и STATIC закрываются;
• RECOVERY FULL – использовать полную модель восстановления;
• BULK_LOGGED — установить модель восстановления BULK_LOGGED;
• SIMPLE – установить простую модель восстановления.

Это основные параметры, которые можно изменить. Более подробно о моделях восстановления можно узнать из файла Doc/BackupRestore.pdf на компакт диске.

Теперь давайте посмотрим на примеры использования этих свойств:

Следующий пример разрешает доступ только одному пользователю:

Доступ только только пользователям ролей db_owner, dbcreator или sysadmin:

Возвращаем нормальный многопользовательский режим:

Вывести базу данных в off-line, т.е. доступ будет запрещен всем пользователям:

Возобновить доступ к базе данных:

Перевести базу данных в режим только для чтения, любые изменения будут отклонены:

Вернуть базе данных полный доступ на запись и чтение:

По завершении транзакции (принятии или откате) все открытые курсоры будут закрываться:

Установить полную модель восстановления:

Установить модель восстановления BULK_LOGGED:

Установить простую модель восстановления:

И последнее, что нам предстоит узнать – это возможность изменения раскладки (кодировки) по умолчанию для базы данных. Для этого выполняется команда:

Exit single-user mode

Currently, my database is in Single User mode. When I try to expand me database, I get an error:

The database ‘my_db’ is not accessible.(ObjectExplorer)

Also, when I try to delete the database, I get the error:

Changes to the state or options of database ‘my_db’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

How do I exit out of single-user mode? I don’t have any user using this database.

When I try to browse my site with IIS, the error I get is:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.