Как в MS SQL Server создать пользователя

На первый взгляд, в MS SQL нестандартная система учетных записей. В ней существуют логины и пользователи.

Логины — это понятие всего сервера SQL. У них могут быть права на какие-то административные задачи (например бэкап). Им можно дать права на какие-то действия с базой или таблицой. Для того что бы у логина были права на базу на основе него создается пользователь. Они могут быть как SQL логином, так и созданные на основе существующих пользователя Windows или AD. Например тот пользователь, который устанавливал MS SQL так же добавился в список логинов. Рекомендуется использовать логины на основе Windows или AD, а не SQL логины.

Пользователи — это понятие в рамках одной базы. Им нельзя дать права по административной части, но можно дать права на работу с базой.

Оба типа учетных записи можно создать как в графике, так и с помощью запроса T-SQL

T-SQL CREATE USER, CREATE LOGIN

Перейдем в базу данных, в которой мы работаем:

Для создания логина SQL выполним:

Anton — имя логина. Password1910 — пароль. У этого логина уже будут права на вход.

Посмотреть что этот логин создался можно так:

Под номером 3 — это кнопка обновления. Может понадобится т.к. в графике изменения происходят не моментально.

Для создания пользователя привязанного к логину нужно выполнить:

Отмечу, что пользователь создасться в той базе, откуда мы выполняем запрос.

Что бы просто создать пользователя нужно:

Где JustUser — просто пользователь

Созданных пользователей можно увидеть так:

Пользователи создаются без каких либо прав на базу.

T-SQL GRANT

GRANT в пер. «Разрешение» дает права на какое-то действие.

Что бы дать пользователю право получать данные из какой-то таблицы нужно выполнить:

Если мы хотим зайти под созданным пользователем нам нужно выполнить ряд действий. Дело в том, что по умолчанию аутентификация по логинам SQL отключена (разрешена только Windows). Для этого зайдем в свойства сервера:

Затем в «Безопасность» и включи аутентификация SQL

Теперь мы сможем зайти под логином SQL. Для этого сделаем следующее — откроем новое окно запросов Ctr + N или нажмем кнопку:

Важно сделать так, что бы у нас было 2 окошка для запросов до того как мы сменем пользователя. Благодаря этому у нас каждое окно будет работать под разными пользователями

Затем окно подключения пользователя:

Меняем тип подключени на SQL и вводим логин/пароль. Я создавал логин Anton, с паролем Password1910.

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

Перейдем к базе, на которую мы давали права. В моем случае это база pubs

Я давал права на SELECT для таблицы dbo.authors. Проверем работу:

Запрос дожен пройти успешно.

Для другой таблицы, на которую мы не давали прав:

The SELECT permission was denied on the object ‘titles’, database ‘pubs’, schema ‘dbo’.

Что бы отозвать разрешение на SELECT нужно заменить GRANT на REVOKE (не забудьте переключится на окно у которого есть разрешение или переключится на предыдущего пользователя):

Разрешений, которые мы можем выдать пользователю достаточно много. Это основные:

Мы можем так же явно запретить пользователю выполнять определенные запросы. Для этого есть DENY:

Что ба дать разрешение на создание таблиц:

Теперь создадим логин через графический интерфейс. Нажмем следующие кнопки:

В новом окне мы должны заполнить логин (1) и пароль (3). Под цифрой 2 мы можем увидеть выбор способа аутентификации. В случае Windows мы должны будем выбрать локального пользователя или пользователя AD. Перейдем на закладку Server Roles (4).

Перед нами находятся роли уровня сервера. Все эти роли это просто набор прав. В Microsoft SQL мы так же можем создать и свои группы. Public — роль по умолчанию для всех, кому разрешено вход. Из всех созданных ролей только public можно изменять.

Приведу описание самых популярных ролей:

Перейдем на следующую вкладку (2)

На этой закладке можно сразу создать пользователя и привязать его к базе данных. На этой закладке отображаются 4 базы, которые вы скорее всего раньше не видели. Это системные базы данных, которые хранят в себе транзакции, структуру подключенных баз, скрипты агента и т.д. В области 2 можно дать права на выделенную базу данных.

Коротко о нескольких ролях баз данных:

Роль public, в отличии от всех остальных, можно изменять. Т.е. можно дать роли public разрешение на SELECT и тогда все пользователи с логином смогут это делать. По умолчанию эта ролько может делать запросы типа:

Создание и удаление пользователей в Microsoft SQL Server

Всем привет! Сейчас мы с Вами рассмотрим примеры создания и удаления пользователей в СУБД Microsoft SQL Server как с использованием инструкций Transact-SQL, так и с использованием среды Management Studio.

Процесс создания пользователей в MS SQL Server включает два этапа:

1. Создание имени входа на SQL Server. Данное имя необходимо, для того чтобы предоставить пользователю возможность подключиться к экземпляру SQL Server;
2. Создание пользователя базы данных. В данном случае мы уже предоставляем пользователю разрешения на объекты базы данных.

Примечание! В качестве SQL сервера у меня для примера будет выступать версия Microsoft SQL Server 2012 Express. На данном SQL сервере создана тестовая база данных Test.

Создание имени входа на MS SQL Server

Прежде чем приступать к созданию имени входа на SQL сервер необходимо определиться с методом аутентификации. Существует два варианта:

1. Проверка подлинности Windows – это когда имя входа может идентифицировать пользователя как учетную запись Windows или как члена группы Windows (в том числе и доменные учетные записи, и группы);
2. Проверка подлинности SQL Server. В данном случае имя входа существует только в SQL Server.

Примечание! Рекомендовано по возможности использовать проверку подлинности Windows. Проверку подлинности SQL Server можно использовать тогда, когда необходимо создать служебное или тестовое имя входа.

Давайте рассмотрим пару примеров создания имени входа на SQL сервер. Сначала мы это сделаем с помощью среды SQL Server Management Studio, а затем с использованием языка Transact-SQL.

Создание имени входа с использованием среды SQL Server Management Studio

Запускаем Management Studio, затем в обозревателе объектов находим пункт «Безопасность», раскрываем его плюсиком, кликаем правой кнопкой мыши по пункту «Имена входа» и выбираем пункт «Создать имя входа».

Далее для примера давайте создадим тестовое имя входа с проверкой подлинности SQL Server. Мы указываем имя входа, придумываем пароль и подтверждаем его. Также мы можем отметить несколько опций, например, использование политики паролей, база данных по умолчанию, язык по умолчанию и другие.

Затем нажимаем на кнопку «ОК», после чего будет создано имя входа TestLogin. По умолчанию данное имя входа будет включено, и оно будет иметь права роли сервера «public».

Создание имени входа с использованием языка Transact-SQL

Для того чтобы создать имя входа на языке Transact-SQL необходимо в Management Studio открыть редактор запросов и выполнить следующую инструкцию (она делает ровно то же самое, что и наши действия выше в графическом интерфейсе Management Studio).

Другими словами для создания имени входа в SQL сервер используется инструкция CREATE LOGIN.

Создание имени входа на SQL Server с проверкой подлинности Windows

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

• ComputerName\NameUser – это Имя компьютера\Имя пользователя;
• FROM WINDOWS – указывает, что будет использоваться проверка подлинности Windows;
• WITH DEFAULT_DATABASE=[Test] – база данных по умолчанию;
• DEFAULT_LANGUAGE=[русский] – язык по умолчанию.

Отключение и включение имен входа в MS SQL Server

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

Создание пользователя базы данных в MS SQL Server

После того как имя входа создано, можно переходить к созданию пользователя базы данных, т.е. сопоставлению пользователя с именем входа.

Давайте создадим пользователя TestLogin также двумя способами, т.е. с помощью Management Studio и языка T-SQL.

Создание пользователя базы данных с помощью Management Studio

Открываем Management Studio, в обозревателе объектов находим нужную базу данных и открываем ее плюсиком. Затем также плюсиком открываем пункт «Безопасность» и кликаем по папке «Пользователи» правой кнопкой мыши и выбираем пункт «Создать пользователя».

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

Также давайте сразу отметим роль базы данных, которую будет иметь данный пользователь. На странице «Членство» я поставил галочку напротив роли db_datareader, т.е. пользователь будет иметь права на чтение данных из пользовательских таблиц. Жмем «ОК».

Создание пользователя базы данных с помощью языка Transact-SQL

Следующая инструкция T-SQL создает пользователя базы данных (схема по умолчанию dbo) и назначает ему роль db_datareader, т.е. делает то же самое, что и мы чуть ранее в графическом интерфейсе Management Studio.

Таким образом, инструкция CREATE USER используется для создания пользователя базы данных.

Удаление пользователя базы данных и имени входа в MS SQL Server

Для того чтобы удалить пользователя базы данных можно написать простую SQL инструкцию, например

Или использовать графический инструмент Management Studio, т.е. в обозревателе объектов, в нужной базе данных выбираем «Безопасность -> Пользователи» и щелкаем правой кнопкой мыши по пользователю, которого необходимо удалить, и выбираем «Удалить».

Примечание! Пользователи, которые владеют защищаемыми объектами, не могут быть удалены из базы данных.

Для удаления имени входа можно также использовать и графический инструмент Management Studio (т.е. «Безопасность -> Имена входа» правой кнопкой мыши по имени, а затем нажать на пункт «Удалить») и инструкцию Transact-SQL т.е.

Примечание! Удалить текущее имя входа нельзя, как и имя входа, владеющее любым защищаемым объектом уровня сервера или заданием агента SQL Server. Также имя входа нельзя удалить, если в данный момент пользователь подключен к системе. Удалить имя входа без удаления сопоставленного пользователя базы данных можно, но это приведет к появлению пользователей, утративших связь с учетными записями.

Заметка! Если Вы хотите освоить язык SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL.

На этом у меня все надеюсь, материал был Вам полезен, пока!

Выбор учетной записи для службы агента SQL Server Select an Account for the SQL Server Agent Service

Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) Управляемый экземпляр SQL Azure Azure SQL Managed Instance Управляемый экземпляр SQL Azure Azure SQL Managed Instance Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) Управляемый экземпляр SQL Azure Azure SQL Managed Instance Управляемый экземпляр SQL Azure Azure SQL Managed Instance

В Управляемом экземпляре Azure SQL в настоящее время поддерживается большинство функций агента SQL Server (но не все). On Azure SQL Managed Instance, most, but not all SQL Server Agent features are currently supported. Подробные сведения см. в статье Различия в T-SQL между Управляемым экземпляром SQL Azure и SQL Server. See Azure SQL Managed Instance T-SQL differences from SQL Server for details.

Стартовая учетная запись службы определяет учетную запись Microsoft Microsoft Windows, с которой запускается агент SQL Server SQL Server , а также его сетевые разрешения. The service startup account defines the Microsoft Microsoft Windows account in which SQL Server SQL Server Agent runs and its network permissions. SQL Server SQL Server выполняется как заданная учетная запись пользователя. Agent runs as a specified user account. Диспетчер конфигурации SQL Server SQL Server позволяет выбрать учетную запись службы агента SQL Server SQL Server из следующих вариантов: You select an account for the SQL Server SQL Server Agent service by using SQL Server SQL Server Configuration Manager, where you can choose from the following options:

Встроенная учетная запись. Built-in account. Может быть выбрана из списка следующих встроенных учетных записей Windows: You can choose from a list of the following built-in Windows service accounts:

Учетная запись Локальная система . Local System account. Имя этой учетной записи — NT AUTHORITY\System. The name of this account is NT AUTHORITY\System. Эта учетная запись имеет неограниченный доступ ко всем локальным системным ресурсам. It is a powerful account that has unrestricted access to all local system resources. Она входит в группу Администраторы на локальном компьютере Windows и поэтому является членом предопределенной роли сервера SQL Server SQL Server sysadmin. It is a member of the Windows Administrators group on the local computer, and is therefore a member of the SQL Server SQL Server sysadmin fixed server role

Параметр С системной учетной записью поддерживается для обратной совместимости. The Local System account option is provided for backward compatibility only. Локальная системная учетная запись обладает разрешениями, которые не нужны для работы агента SQL Server SQL Server . The Local System account has permissions that SQL Server SQL Server Agent does not require. Старайтесь не запускать агент SQL Server SQL Server от имени учетной записи локальной системы. Avoid running SQL Server SQL Server Agent as the Local System account. В целях безопасности рекомендуется пользоваться учетной записью домена Windows с разрешениями, перечисленными в подразделе «Разрешения учетной записи домена Windows» ниже в этом разделе. For improved security, use a Windows domain account with the permissions listed in the following section, «Windows Domain Account Permissions.»

Указанная учетная запись. This account. Позволяет задать учетную запись домена Windows, с которой выполняется служба агента SQL Server SQL Server . Lets you specify the Windows domain account in which the SQL Server SQL Server Agent service runs. Рекомендуется выбирать учетную запись пользователя Windows, не входящего в группу Администраторы . We recommend choosing a Windows user account that is not a member of the Windows Administrators group. Однако существуют ограничения при администрировании нескольких серверов, когда учетная запись службы агента SQL Server SQL Server не входит в локальную группу Администраторы . However, there are limitations for using multiserver administration when the SQL Server SQL Server Agent service account is not a member of the local Administrators group. Дополнительные сведения см. в подразделе «Поддерживаемые типы учетных записей» далее в этом разделе. For more information, see ‘Supported Service Account Types’ that follows in this topic.

Разрешения учетной записи домена Windows Windows Domain Account Permissions

В целях повышения безопасности выбирайте пункт Указанная учетная запись, соответствующий учетной записи домена Windows. For improved security, select This account, which specifies a Windows domain account. Заданная учетная запись домена Windows должна обладать следующими разрешениями: The Windows domain account that you specify must have the following permissions:

• Разрешение на вход в систему в качестве службы во всех версиях Windows (SeServiceLogonRight) In all Windows versions, permission to log on as a service (SeServiceLogonRight)

Служба агента SQL Server SQL Server должна быть членом группы «Доступ, совместимый с версиями Windows до 2000» контроллера домена. В противном случае задания, принадлежащие пользователям домена, которые не являются администраторами Windows, выполняться не будут. The SQL Server SQL Server Agent service account must be part of the Pre-Windows 2000 Compatible Access group on the domain controller, or jobs that are owned by domain users who are not members of the Windows Administrators group will fail.

На серверах Windows учетной записи, с которой выполняется служба агента SQL Server SQL Server , для поддержки посредников агента SQL Server SQL Server необходимы следующие разрешения: In Windows servers, the account that the SQL Server SQL Server Agent Service runs as requires the following permissions to be able to support SQL Server SQL Server Agent proxies.

Разрешение на обход перекрестной проверки (SeChangeNotifyPrivilege) Permission to bypass traverse checking (SeChangeNotifyPrivilege)

Разрешение на замену токена уровня процесса (SeAssignPrimaryTokenPrivilege) Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)

Разрешение на выделение процессам квот памяти (SeIncreaseQuotaPrivilege) Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)

Разрешение на доступ к этому компьютеру из сети (SeNetworkLogonRight) Permission to access this computer from the network (SeNetworkLogonRight)

Если учетная запись не обладает разрешениями на поддержку посредников, то создавать задания могут только члены предопределенной роли сервера sysadmin . If the account does not have the permissions required to support proxies, only members of the sysadmin fixed server role can create jobs.

Чтобы получать уведомление о предупреждении инструментария WMI, учетной записи службы агента SQL Server SQL Server должно быть предоставлено разрешение на пространство имен, содержащее события WMI и ALTER ANY EVENT NOTIFICATION. To receive WMI alert notification, the service account for SQL Server SQL Server Agent must have been granted permission to the namespace that contains the WMI events, and ALTER ANY EVENT NOTIFICATION.

Членство в ролях SQL Server SQL Server Role Membership

Учетная запись, от которой запускается служба агента SQL Server SQL Server , должна быть членом следующих ролей SQL Server SQL Server : The account that the SQL Server SQL Server Agent service runs as must be a member of the following SQL Server SQL Server roles:

Учетная запись должна быть членом предопределенной роли сервера sysadmin . The account must be a member of the sysadmin fixed server role.

Чтобы использовать обработку заданий в многосерверной среде, учетная запись должна быть членом роли базы данных msdb****TargetServersRole на главном сервере. To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server.

Поддерживаемые типы учетных записей Supported Service Account Types

В следующей таблице перечислены типы учетных записей Windows, которые могут быть использованы для службы агента SQL Server SQL Server . The following table lists the Windows account types that can be used for the SQL Server SQL Server Agent service.

Тип учетной записи Service account type	Некластеризованный сервер Nonclustered Server	Кластеризованный сервер Clustered server	Контроллер домена (некластеризованный) Domain controller (nonclustered)
Microsoft Microsoft Учетная запись Windows (член группы «Администраторы» Windows) Windows domain account (member of Windows Administrators group)	Поддерживается Supported	Поддерживается Supported	Поддерживается Supported
Неадминистративная учетная запись домена Windows Windows domain account (non-administrative)	Поддерживается Supported См. ограничение № 1 ниже. See Limitation 1 below.	Поддерживается Supported См. ограничение № 1 ниже. See Limitation 1 below.	Поддерживается Supported См. ограничение № 1 ниже. See Limitation 1 below.
Учетная запись сетевой службы (NT AUTHORITY\NetworkService) Network Service account (NT AUTHORITY\NetworkService)	Поддерживается Supported См. ограничения № 1, 2 и 4 ниже. See Limitation 1, 3, and 4 below.	Не поддерживается Not supported	Не поддерживается Not supported
Неадминистративная учетная запись локального пользователя Local user account (non-administrative)	Поддерживается Supported См. ограничение № 1 ниже. See Limitation 1 below.	Не поддерживается Not supported	Неприменимо Not applicable
Учетная запись Local System (NT AUTHORITY\System) Local System account (NT AUTHORITY\System)	Поддерживается Supported См. ограничение № 2 ниже. See Limitation 2 below.	Не поддерживается Not supported	Поддерживается Supported См. ограничение № 2 ниже. See Limitation 2 below.
Учетная запись локальной службы (NT AUTHORITY\NetworkService) Local Service account (NT AUTHORITY\LocalService)	Не поддерживается Not supported	Не поддерживается Not supported	Не поддерживается Not supported

Ограничение 1. Использование неадминистративных учетных записей для администрирования нескольких серверов Limitation 1: Using Non-administrative Accounts for Multiserver Administration

Прикрепление целевого сервера к главному серверу может завершиться ошибкой, после чего появляется следующее сообщение: «Не удалось выполнить операцию прикрепления». Enlisting target servers to a master server may fail with the following error message: «The enlist operation failed.»

Чтобы устранить эту ошибку, перезапустите SQL Server SQL Server и службы агента SQL Server SQL Server . To resolve this error, restart both the SQL Server SQL Server and the SQL Server SQL Server Agent services. Дополнительные сведения см. в статье Iniciar, parar, pausar, retomar e reiniciar os serviços SQL Server. For more information, see Start, Stop, Pause, Resume, Restart the Database Engine, SQL Server Agent, or SQL Server Browser Service.

Ограничение 2. Использование учетной записи Local System для администрирования нескольких серверов Limitation 2: Using the Local System Account for Multiserver Administration

Администрирование нескольких серверов поддерживается при выполнении службы агента SQL Server SQL Server под учетной записью Local System только в том случае, если целевой и главный серверы расположены на одном и том же компьютере. Multiserver administration is supported when the SQL Server SQL Server Agent service is run under the Local System account only when both the master server and the target server reside on the same computer. При использовании этой конфигурации, при прикреплении целевого сервера к главному серверу, возвращается следующее сообщение: If you use this configuration, the following message is returned when you enlist target servers to the master server:

«Убедитесь, что стартовая учетная запись агента для имеет права для входа на сервер targetServer». «Ensure the agent start-up account for has rights to log on as targetServer.»

Данное сообщение можно пропустить. You can ignore this informational message. Операция прикрепления должна быть завершена успешно. The enlistment operation should complete successfully. Дополнительные сведения см. в статье Создание многосерверной среды. For more information, see Create a Multiserver Environment.

Ограничение 3. Использование учетной записи сетевой службы, которая является учетной записью SQL Server Limitation 3: Using the Network Service Account When It Is a SQL Server User

SQL Server SQL Server При запуске агента SQL Server SQL Server может произойти сбой, если он запускается под учетной записью сетевой службы, которая уже явным образом получила доступ к экземпляру SQL Server SQL Server в качестве пользователя SQL Server SQL Server . Agent may fail to start if you run the SQL Server SQL Server Agent service under the Network Service account, and the Network Service account has been explicitly granted access to log into a SQL Server SQL Server instance as a SQL Server SQL Server user.

Для решения этой проблемы перезагрузите компьютер, на котором работает SQL Server SQL Server . To resolve this, reboot the computer where SQL Server SQL Server is running. Это действие необходимо выполнить однократно. This only needs to be done once.

Ограничение 4. Использование учетной записи сетевой службы при выполнении служб SQL Server Reporting Services на том же самом компьютере Limitation 4: Using the Network Service Account When SQL Server Reporting Services Is Running on the Same Computer

SQL Server SQL Server Агент не может быть запущен, если служба агента SQL Server SQL Server выполняется под учетной записью сетевой службы, а службы Службы Reporting Services Reporting Services запущены на этом же самом компьютере. Agent may fail to start if you run the SQL Server SQL Server Agent service under the Network Service account and Службы Reporting Services Reporting Services is also running on the same computer.

Для решения этой проблемы перезагрузите компьютер, на котором работает SQL Server SQL Server , а затем перезапустите службу SQL Server SQL Server и службу агента SQL Server SQL Server . To resolve this, reboot the computer where SQL Server SQL Server is running, and then restart both the SQL Server SQL Server and the SQL Server SQL Server Agent services. Это действие необходимо выполнить однократно. This only needs to be done once.

Общие задачи Common Tasks

Указание стартовой учетной записи службы агента SQL Server To specify the startup account for the SQL Server Agent service

Указание профиля электронной почты агента SQL Server To specify the mail profile for SQL Server Agent

Запуск агента SQL Server SQL Server во время старта операционной системы задается с помощью диспетчера конфигурации SQL Server SQL Server . Use SQL Server SQL Server Configuration Manager to specify that SQL Server SQL Server Agent must start up when the operating system starts.