Создание и удаление пользователей в 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, затем в обозревателе объектов находим пункт «Безопасность», раскрываем его плюсиком, кликаем правой кнопкой мыши по пункту «Имена входа» и выбираем пункт «Создать имя входа».

Скриншот 1

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

Скриншот 2

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

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

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

Скриншот 3

   
   CREATE LOGIN [TestLogin] 
        WITH PASSWORD=N'Pa$$w0rd', 
        DEFAULT_DATABASE=[Test], 
        DEFAULT_LANGUAGE=[русский], 
        CHECK_EXPIRATION=OFF, 
        CHECK_POLICY=ON
   GO

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

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

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

   
   CREATE LOGIN [ComputerName\NameUser] 
        FROM WINDOWS 
        WITH DEFAULT_DATABASE=[Test], 
        DEFAULT_LANGUAGE=[русский];
   GO

Где,

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

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

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

   
   --Отключение
   ALTER LOGIN TestLogin DISABLE;
   --Включение
   ALTER LOGIN TestLogin ENABLE;

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

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

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

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

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

Скриншот 4

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

Скриншот 5

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

Скриншот 6

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

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

   
   USE Test
   GO
   CREATE USER [TestLogin] FOR LOGIN [TestLogin] WITH DEFAULT_SCHEMA=[dbo]
   GO
   ALTER ROLE [db_datareader] ADD MEMBER [TestLogin];
   GO

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

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

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

   
   DROP USER Testlogin;

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

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

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

   
   DROP LOGIN TestLogin;

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

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

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 1
  1. Админ (автор)

    Приглашаю всех желающих пройти мои онлайн-курсы по изучению языка T-SQL – https://self-learning.ru/courses/t-sql
    На курсах используется моя авторская последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL. Каждый курс включает огромное количество материалов: видео, текстовый материал, тесты, домашние задания, скрипты, а также сертификат о прохождении.
    На курсах Вы можете заниматься в комфортном для себя темпе не выходя из дома в любое удобное для Вас время.

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.