Добавление пользователей базы данных
Пользователь может войти в систему баз данных, используя учетную запись пользователя Windows или регистрационное имя входа в SQL Server. Для последующего доступа и работы с определенной базой данных пользователь также должен иметь учетную запись пользователя базы данных. Для работы с каждой отдельной базой данных требуется иметь учетную запись пользователя именно для этой базы данных. Учетную запись пользователя базы данных можно сопоставить с существующей учетной записью пользователя Windows, группой Windows (в которой пользователь имеет членство), регистрационным именем или ролью.
Управлять пользователями баз данных можно с помощью среды Management Studio или инструкций языка Transact-SQL. Оба эти способа рассматриваются в следующих подразделах.
Управление пользователями базы данных с помощью среды Management Studio
Чтобы добавить пользователя базы данных с помощью среды Management Studio, разверните узел сервера в окне Object Explorer и в нем папку «Databases», в этой папке разверните узел требуемой базы данных, а в ней папку «Security». Щелкните правой кнопкой мыши папку «Users» и в контекстном меню выберите пункт New User. Откроется диалоговое окно Database User — New, в котором следует ввести имя пользователя User name и выбрать соответствующее регистрационное имя Login name:
Здесь можно также выбрать схему по умолчанию для данного пользователя.
Управление безопасностью базы данных посредством инструкций языка Transact-SQL
Для добавления пользователя в текущую базу данных используется инструкция CREATE USER. Синтаксис этой инструкции выглядит таким образом:
Параметр user_name определяет имя, по которому пользователь идентифицируется в базе данных, а в параметре login указывается регистрационное имя, для которого создается данный пользователь. В параметрах cert_name и key_name указываются соответствующий сертификат и асимметричный ключ соответственно. Наконец, в параметре WITH DEFAULT_SCHEMA указывается первая схема, с которой сервер базы данных будет начинать поиск для разрешения имен объектов для данного пользователя базы данных.
Применение инструкции CREATE USER показано в примере ниже:
USE SampleDb; CREATE USER Vasya FOR LOGIN Vasya; CREATE USER Alex FOR LOGIN [ProfessorWeb\Alexandr] WITH DEFAULT_SCHEMA = poco;Для успешного выполнения на вашем компьютере второй инструкции примера требуется сначала создать учетную запись Windows для пользователя Alexandr и вместо домена (сервера) ProfessorWeb указать имя вашего сервера.
В этом примере первая инструкция CREATE USER создает пользователя базы данных Vasya для пользователя Vasya учетной записи Windows. Схемой по умолчанию для пользователя Vasya будет dbo, поскольку для параметра DEFAULT_SCHEMA значение не указано. Вторая инструкция CREATE USER создает нового пользователя базы данных Alex. Схемой по умолчанию для этого пользователя будет схема poco. (Параметру DEFAULT_SCHEMA можно присвоить в качестве значения схему, которая в данное время не существует в базе данных.)
Каждая база данных имеет своих конкретных пользователей. Поэтому инструкцию CREATE USER необходимо выполнить для каждой базы данных, для которой должна существовать учетная запись пользователя. Кроме этого, для определенной базы данных регистрационное имя входа в SQL Server может иметь только одного пользователя базы данных.
С помощью инструкции ALTER USER можно изменить имя пользователя базы данных, изменить схему пользователя по умолчанию или переопределить пользователя с другим регистрационным именем. Подобно инструкции CREATE USER, пользователю можно присвоить схему по умолчанию прежде, чем она создана.
Для удаления пользователя из текущей базы данных применяется инструкция DROP USER. Пользователя, который является владельцем защищаемых объектов (объектов базы данных), удалить нельзя.
Схемы базы данных по умолчанию
Каждая база данных в системе имеет следующие схемы по умолчанию:
Компонент Database Engine позволяет пользователям, которые не имеют учетной записи пользователя, работать с базой данных, используя схему guest. (Каждая созданная база данных имеет эту схему.) Для схемы guest можно применять разрешения таким же образом, как и для любой другой схемы. Кроме этого, схему guest можно удалить из любой базы данных, кроме системных баз данных master и tempdb.
Каждый объект базы данных принадлежит одной, и только одной схеме, которая является схемой по умолчанию для данного объекта. Схема по умолчанию может быть определена явно или неявно. Если при создании объекта его схема по умолчанию не определена явно, этот объект принадлежит к схеме dbo. Кроме этого, при использовании принадлежащей ему базы данных регистрационное имя всегда имеет специальное имя пользователя dbo.
Вся информация о схемах содержится в схеме information_schema. Схема sys, как можно догадаться, содержит системные объекты, такие как представления каталога.
Урок 2. Настройка разрешений для объектов базы данных
Схема обучения Приступая к работе с запросами с помощью Transact-SQL содержит более подробные материалы, а также практические примеры.
Предоставление пользователю доступа к базе данных включает три шага. Вначале создается имя входа. Имя входа дает пользователю возможность подключиться к компоненту Компонент SQL Server Database Engine. Затем имя входа настраивается как пользователь в заданной базе данных. Наконец, предоставляются пользовательские разрешения на объекты базы данных. В этом занятии показаны все три шага, а также создание представления и хранимой процедуры в виде объекта.
В этом занятии используются объекты, созданные в разделе Урок 1. Создание объектов баз данных. Пройдите урок 1, прежде чем переходить к уроку 2.
Предварительные требования
Для работы с этим руководством необходима среда SQL Server Management Studio и доступ к экземпляру SQL Server.
- Установите SQL Server Management Studio.
Если у вас нет доступа к экземпляру SQL Server, выберите свою платформу в следующих ссылках. При выборе проверки подлинности SQL используйте учетные данные SQL Server.
- Windows: скачайте SQL Server 2022 Developer Edition.
- Linux: скачайте SQL Server 2022 в контейнер.
Мы слушаем! Если вы обнаружили в этой статье устаревшие или недостоверные сведения, например инструкции или пример кода, сообщите нам. Можно воспользоваться кнопкой Эта страница в разделе Отзывы внизу страницы. Обычно мы читаем отзывы про материалы по SQL на следующий день. Благодарим вас.
Создает вход
Чтобы получить доступ к компоненту Компонент Database Engine, необходимо иметь имя входа. Имя входа может идентифицировать пользователя как учетную запись Windows или как члена группы Windows, или имя входа может быть именем входа SQL Server , которое существует только в SQL Server. При возможности используйте проверку подлинности Windows.
По умолчанию администраторы компьютера имеют полный доступ к SQL Server. Для этого занятия нужно иметь пользователя с меньшим правом доступа; следовательно, вы создадите новую локальную учетную запись проверки подлинности Windows на компьютере. Чтобы сделать это, нужно быть администратором на своем компьютере. Затем вы предоставляете новому пользователю доступ к SQL Server.
Создание учетной записи Windows
- Нажмите кнопку Пуск, выберите Выполнить, в поле Открыть введите %SystemRoot%\system32\compmgmt.msc /s и нажмите кнопку ОК , чтобы открыть программу управления компьютером.
- В разделе Системные инструменты разверните узел Локальные пользователи и группы, щелкните правой кнопкой мыши Элемент Пользователи, а затем выберите Новый пользователь.
- В поле Имя пользователя введите Mary.
- В поле Пароль и Подтверждение пароля введите надежный пароль, а затем выберите Создать , чтобы создать локального пользователя Windows.
Создание имени для входа SQL
В окне редактора запросов среды SQL Server Management Studioвведите и выполните следующий исходный код, заменив computer_name на имя компьютера. FROM WINDOWS указывает, что Windows выполняет проверку подлинности пользователя. Необязательный DEFAULT_DATABASE аргумент подключается Mary к TestData базе данных, если только строка подключения не указывает другую базу данных. Эта инструкция рассматривает точку с запятой в виде необязательного завершения инструкции языка Transact-SQL.
CREATE LOGIN [computer_name\Mary] FROM WINDOWS WITH DEFAULT_DATABASE = [TestData]; GO Этим авторизируется имя пользователя Mary , проверенное компьютером, чтобы получить доступ к экземпляру SQL Server. Если на компьютере находится более одного экземпляра SQL Server , нужно создать имя входа для каждого экземпляра, к которому Mary должна иметь доступ.
Поскольку Mary не является доменной учетной записью, это имя пользователя может быть принято только на данном компьютере.
Предоставление доступа к базе данных
Теперь учетная Mary запись имеет доступ к этому экземпляру SQL Server, но не имеет разрешения на доступ к базам данных. Учетная запись даже не будет иметь доступа к базе данных TestData по умолчанию, пока вы не авторизуете учетную запись в качестве пользователя базы данных.
Чтобы предоставить Mary доступ, переключитесь на TestData базу данных, а затем используйте инструкцию CREATE USER, чтобы сопоставить имя входа с пользователем с именем Mary .
Создание пользователя в базе данных
Введите и выполните следующие инструкции (заменяя computer_name на имя компьютера), чтобы предоставить пользователю Mary доступ к базе данных TestData .
USE [TestData]; GO CREATE USER [Mary] FOR LOGIN [computer_name\Mary]; GO Теперь пользователь Mary имеет доступ к SQL Server и к базе данных TestData .
Создание представлений и хранимых процедур
Как администратор, вы можете выполнить select из Products таблицы и vw_Names представления, а также выполнить процедуру pr_Names , но Мэри не может. Чтобы предоставить Mary необходимые разрешения, воспользуйтесь инструкцией GRANT.
Предоставление разрешений на хранимые процедуры
Выполните следующую инструкцию, чтобы предоставить Mary разрешение на EXECUTE для хранимой процедуры pr_Names .
GRANT EXECUTE ON pr_Names TO Mary; GO В этом сценарии Мэри может получить доступ к Products таблице только с помощью хранимой процедуры. Если Mary нужно выполнять инструкцию SELECT к представлению, нужно выполнить инструкцию GRANT SELECT ON vw_Names TO Mary . Чтобы удалить доступ к объектам базы данных, воспользуйтесь инструкцией REVOKE.
Если таблицей, представлением или хранимой процедурой не владеет та же схема, процесс предоставления прав становится более сложным.
Об инструкции GRANT
Нужно иметь разрешение на EXECUTE, чтобы выполнить хранимую процедуру. Нужно иметь разрешения на SELECT, INSERT, UPDATE и DELETE, чтобы получить доступ к данным и изменять их. Инструкция GRANT также используется для других разрешений, например для разрешений на создание таблиц.
Дальнейшие действия
В следующей статье вы узнаете, как удалить объекты базы данных, созданные в других уроках.
Дополнительные сведения см. в следующей статье:
Код Икс Пи ☆ Центр автоматизации, сопровождения и поддержки
Иногда бывает необходимо узнать, кто именно сейчас работает в базе данных или в базах данных на сервере MSSQL 2008/2012. Например, для того чтобы принудительно завершить все эти сеансы или просто узнать, кто именно нагружает сервер запросами. Сегодня мы научимся с Вами это делать, используя при этом простые запросы к системным представлениям на Transact-SQL.
Как Вы уже поняли, сегодня речь пойдет об активных сеансах и процессах в СУБД MSSQL, которые мы будем получать, используя системное представление sys.sysprocesses.
Содержит сведения о процессах, которые выполняются в экземпляре SQL Server. Эти процессы могут быть клиентскими или системными. Для доступа к sysprocesses либо необходимо быть в контексте главной базы данных, либо следует использовать трехчастное имя master.dbo.sysprocesses.
Для того чтобы понимать, что такое системное представление, советую Вам для начала ознакомиться с понятием простого представления, которое рассматривается в статье — Зачем нужны представления (views) в базах данных. Также мы будем писать пусть простые, но все запросы, с основами которых Вы естественно должны быть знакомы, если нет, то можете прочитать статью основы языка SQL — оператор select.
Вроде бы все, что необходимо знать, перед тем как что-то пробовать из ниже приведенных примеров на практике я указал, переходим непосредственно к самой сути сегодняшнего вопроса.
Как узнать активные сеансы пользователей
Системное представление sys.sysprocesses содержит текущее состояние сервера на предмет запущенных процессов, исходя из этого, напишем простенький запрос:
select db_name(dbid) as db, spid as idproc, loginame, program_name, status from sys.sysprocessesdb – это база данных, в которой запущен процесс;
idproc – идентификатор процесса;
loginame – логин, т.е. кто именно запустил;
program_name – приложение, из которого запущен процесс;
status – соответственно статус.
Статусы бывают разные, например,
Runnable – активный процесс, т.е. например, в данный момент выполняется какой-нибудь запрос;
Sleeping – режим ожидания, т.е. например, окно запроса открыто, но в данный момент он не запущен;
Background – запущен в фоновом режиме.
Если необходимо узнать, кто именно работает конкретно в той или иной базе можно добавить условие:
select db_name(dbid) as db, spid as idproc, loginame, program_name, status from sys.sysprocesses where db_name(dbid) = 'name_db'Соответственно вместо name_db пишите свою нужную базу данных, и результат будет получен только по этой базе данных.
Как завершить все активные сеансы пользователей
Это необходимо, например, для того чтобы получить монопольный доступ к базе данных, а для чего он Вам может понадобится это уже Ваше дело, например, чтобы восстановить базу данных. Мы кстати рассматривали процесс восстановления базы данных, правда, на MSSql 2000.
Для того чтобы завершить все подключения пользователей к бд, выполним следующий запрос:
set nocount on declare @dbname varchar(100) declare @query varchar(max) set @query = '' set @dbname = 'name_db' select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; ' from sys.sysprocesses where dbid=db_id(@dbname) if len(@query) > 0 begin exec(@query) end@dbname – переменная, для того чтобы указать к какой базе необходимо завершить все подключения;
@query – переменная для хранения запроса;
В конструкции select мы динамически формируем запрос с идентификаторами процессов, которые необходимо завершить. Далее в переменной @query будет храниться запросы вида
kill 58; kill 61; kill 70;которые мы выполним через exec(@query) и тем самым завершим все процессы.
Из всего выше сказанного Вы легко можете узнавать, кто именно сейчас выполняет запрос и даже завершить его, что в свою очередь иногда бывает полезно.
Также хотелось бы сказать, что в MSSQL существует встроенный «Монитор активности». Его можно вызвать, нажав правой кнопкой по серверу, в окне «обозреватель объектов» и вызвать окно «Монитор активности», на котором будет располагаться список свойств, которые Вы можете развернуть для подробного просмотра, где в свою очередь и будет отображать вся текущая активность на сервере.
Описание всех полей sysprocesses
| Имя столбца | Тип данных | Описание |
|---|---|---|
| spid | smallint | Идентификатор сеанса SQL Server. |
| kpid | smallint | Идентификатор потока Windows. |
| blocked | smallint | Идентификатор сеанса, блокирующего данный запрос. Если этот столбец содержит значение NULL, то запрос не блокирован или сведения о сеансе блокировки недоступны (или не могут быть идентифицированы). |
-2 = Блокирующий ресурс принадлежит потерянной распределенной транзакции.
-3 = Блокирующий ресурс принадлежит отложенной транзакции восстановления.
Неактивные = SQL Server сбрасывает сеанс.
под управлением = сеанс запущен один или несколько пакетов. Если включен режим MARS, в сеансе может выполняться несколько пакетов.
фон = сеанса запущена фоновая задача, например обнаружение взаимоблокировок.
откат = сеанс имеет отката транзакции в процессе.
Ожидание = сеанс ожидает доступности рабочего потока.
готов к запуску = задача в сеансе находится в очереди исполнителей планировщика, ожидая времени такта.
spinloop = задача сеанса ожидает освобождения объекта взаимоблокировки.
Свойства подключения к базе данных
Окно Свойства подключения к базе (Рис. 1) открывается после нажатия на кнопку Добавить или на кнопку Свойства в окне Выбор базы данных, а также с помощью пункта контекстного меню Свойства (см. Окно списка баз данных).
Рисунок 1. Окно Свойства подключения к базе
В поле Сервер базы данных выбирается сервер, на котором находится существующая база данных или будет создана новая. Имя сервера также может быть введено вручную. Имя сервера имеет вид «\» (для именованного экземпляра SQL Server, например, SERVER\SQLEXPRESS) или «» (для экземпляра по умолчанию). В качестве имени компьютера можно указать как NetBIOS-имя (обычное сетевое имя, например, SERVER), так и FQDN (полное доменное имя, например, server.mydomain.ru) или IP-адрес компьютера (например, 192.168.1.184).
В поле База данных показываются все физические базы данных Microsoft SQL Server на сервере базы данных, к которым пользователю открыт доступ. Для настройки подключения к одной из баз выберите её имя в списке и нажмите на кнопку ОК. Окно Свойства подключения к базе закроется, в список баз добавится новая база данных.
В поле Пользовательское название базы вводится произвольное название базы данных, которое будет видеть пользователь в списке баз. Рекомендуется формировать название таким образом, чтобы пользователь понимал, с какой информацией он будет работать.
Описание кнопок окна Свойства подключения к базе приведено в Таблице 1.
| Кнопка | Описание |
|---|---|
| Новая… | Открывает окно Новая база для создания новой базы данных Microsoft SQL Server (см. Создание новой базы данных). |
| Загрузить… | Открывает окно Новая база для загрузки базы данных из файла (см. Загрузка базы данных из файла). |
| Сохранить… | Позволяет сохранить базу данных в файл (см. Сохранение базы данных в файл). |
| Дополнительно | Используется для смены сервера лицензий, а также для специального преобразования базы данных, например, при переходе на новую версию (см. Свойства базы данных). |
| OK | Закрывает окно Свойства подключения к базе. Если в окне Свойства подключения к базе производилось добавление новой базы, то она будет добавлена в список баз данных. Если в окне Свойства подключения к базе вносились изменения в параметры подключения базы, то изменения будут сохранены. |
| Отмена | Закрывает окно Свойства подключения к базе и отменяет добавление базы или изменение её свойств. |
Таблица 1. Кнопки окна «Свойства подключения к базе»
Опция SQL аутентификация
Программа поддерживает два режима аутентификации: Windows и SQL . По умолчанию флажок SQL аутентификация не установлен и для доступа к базе данных действует аутентификация Windows. SQL аутентификацию следует применять в одноранговых и смешанных сетях.
Если установить флажок SQL аутентификация, то для доступа к базе данных можно воспользоваться логином и паролем пользователя базы данных Microsoft SQL Server, которые должен предоставить администратор базы данных (см. Назначение пользователей и администраторов баз данных).
SQL аутентификация используется только для входа в базу данных. Во время работы в базе пользователь идентифицируется как доменный или локальный пользователь, работающий с системой.
Подробнее про SQL аутентификацию см. SQL аутентификация.