Как создать пользователя в sql server

При работе с SQL Server контроль доступа к данным реализуется через учетные записи пользователей. Создание пользователя включает несколько этапов: от настройки входа (login) до привязки к базе данных и назначения ролей. Каждый шаг имеет значение для безопасности и стабильности работы системы.

В SQL Server пользователь – это объект базы данных, ассоциированный с учетной записью входа (login), зарегистрированной на уровне экземпляра сервера. Без этого соответствия невозможно предоставить доступ к конкретной базе данных. Именно поэтому первым шагом является создание логина с использованием команды CREATE LOGIN.

После создания логина необходимо переключиться на нужную базу данных и создать в ней пользователя через команду CREATE USER, указав связь с ранее созданным логином. Без этой связи пользователь не сможет выполнять действия в рамках базы данных, даже если логин существует на уровне сервера.

Следующий критически важный шаг – назначение ролей. С помощью команды ALTER ROLE пользователь добавляется в одну или несколько ролей, определяющих его права: от чтения данных до полного администрирования. Например, роль db_datareader дает доступ к чтению всех таблиц, а db_owner предоставляет полный контроль.

Процесс создания пользователя должен завершаться проверкой прав доступа через запросы к системным представлениям sys.database_principals и sys.database_role_members. Это позволяет убедиться, что настройки безопасности соответствуют требованиям приложения или политики организации.

Подключение к SQL Server через SQL Server Management Studio

Запустите SQL Server Management Studio (SSMS). В открывшемся окне подключения укажите следующие параметры:

При использовании Windows Authentication учётные данные текущего пользователя подставляются автоматически. Для SQL Server Authentication убедитесь, что сервер настроен в режиме смешанной аутентификации. Это можно проверить и изменить через свойства экземпляра SQL Server в SQL Server Configuration Manager или с помощью запроса:

EXEC xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode';

Если значение LoginMode равно 2 – смешанный режим включён. Для изменения режима потребуется перезапуск службы SQL Server после изменения параметра в конфигурации.

После ввода всех параметров нажмите кнопку Connect. При успешном подключении слева отобразится панель Object Explorer с деревом баз данных и служб SQL Server.

Создание логина на уровне сервера с помощью T-SQL

Для создания логина на уровне экземпляра SQL Server используется команда CREATE LOGIN. Логин необходим для аутентификации пользователя на уровне сервера и может быть основан на SQL Server-аутентификации или интегрироваться с Windows.

Пример создания логина с SQL Server-аутентификацией:

CREATE LOGIN [UserLoginName]
WITH PASSWORD = 'СложныйПароль123!',
CHECK_EXPIRATION = OFF,
CHECK_POLICY = ON;

Параметр CHECK_POLICY включает проверку сложности и истории пароля согласно политике Windows. CHECK_EXPIRATION управляет сроком действия пароля. Для тестовой среды можно отключить обе проверки, но в продуктиве следует соблюдать политику безопасности.

Пример логина с Windows-аутентификацией:

CREATE LOGIN [DOMAIN\ИмяПользователя]
FROM WINDOWS;

После создания логина необходимо выдать соответствующие разрешения или назначить роли. Без этого логин не сможет подключаться к базам данных. Для назначения логина на конкретную базу следует использовать команду CREATE USER уже в контексте выбранной базы данных.

Назначение пароля при создании SQL логина

При создании SQL логина необходимо задать надежный пароль, соответствующий требованиям политики безопасности SQL Server. Используйте команду CREATE LOGIN с параметром WITH PASSWORD, указывая строку пароля в кавычках.

Пример:

CREATE LOGIN TestUser WITH PASSWORD = 'Str0ng_P@ssw0rd!';

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

Чтобы отключить проверку политики, добавьте CHECK_POLICY = OFF. Однако делать это не рекомендуется в продуктивной среде:

CREATE LOGIN TestUser WITH PASSWORD = 'SimplePass123', CHECK_POLICY = OFF;

Для предотвращения изменения пароля пользователем можно указать MUST_CHANGE = OFF. По умолчанию, если политика активна, SQL Server требует смены пароля при первом входе:

CREATE LOGIN TestUser WITH PASSWORD = 'Str0ng_P@ssw0rd!' MUST_CHANGE = OFF;

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

Регулярно проверяйте актуальность паролей и наличие логинов с устаревшими или скомпрометированными паролями, используя представление sys.sql_logins.

Создание пользователя базы данных, связанного с логином

Чтобы связать пользователя базы данных с существующим логином SQL Server, выполните следующие действия в заданной последовательности:

1. Подключитесь к нужному экземпляру SQL Server через SQL Server Management Studio (SSMS).
2. Откройте новую вкладку запроса и выберите целевую базу данных с помощью команды:

   USE [ИмяБазыДанных];

3. Создайте пользователя, указав имя логина, с которым будет установлена связь:

   CREATE USER [ИмяПользователя] FOR LOGIN [ИмяЛогина];

4. При необходимости назначьте роли для управления правами доступа. Пример добавления в роль db_datareader:

   ALTER ROLE db_datareader ADD MEMBER [ИмяПользователя];

Особенности:

• Имя пользователя может совпадать с логином, но это не обязательно.
• Если логин уже существует, повторное создание вызовет ошибку – проверьте его наличие командой:

  SELECT name FROM sys.server_principals WHERE name = 'ИмяЛогина';

• Для просмотра пользователей текущей базы используйте:

  SELECT name FROM sys.database_principals WHERE type_desc = 'SQL_USER';

• Не путайте пользователя базы данных с логином – логин обеспечивает доступ к серверу, пользователь определяет права в конкретной базе.

Предоставление прав на чтение и запись для пользователя

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

1. Откройте SQL Server Management Studio (SSMS) и выполните подключение к нужному серверу.
2. Выберите базу данных, к которой требуется предоставить доступ, и выполните следующую команду, чтобы назначить пользователя:

   USE [ИмяБазыДанных];
   CREATE USER [ИмяПользователя] FOR LOGIN [ИмяЛогина];

3. Добавьте пользователя в роли db_datareader и db_datawriter:

   ALTER ROLE db_datareader ADD MEMBER [ИмяПользователя];
   ALTER ROLE db_datawriter ADD MEMBER [ИмяПользователя];

Роль db_datareader предоставляет доступ на чтение всех таблиц базы данных. Роль db_datawriter даёт право на вставку, обновление и удаление данных во всех таблицах. Этого достаточно для большинства сценариев работы приложения или пользователя с базой данных.

Если пользователь должен иметь доступ только к отдельным таблицам, назначайте разрешения через GRANT вместо добавления в роли. Пример:

GRANT SELECT, INSERT, UPDATE ON dbo.ИмяТаблицы TO [ИмяПользователя];

Контролируйте объем предоставляемых прав. Избыточный доступ увеличивает риски безопасности.

Добавление пользователя в существующие роли базы данных

Чтобы предоставить пользователю доступ к определённым правам и функциям базы данных, необходимо добавить его в соответствующие роли. SQL Server использует встроенные роли, такие как db_owner, db_datareader, db_datawriter и другие, которые обладают различными уровнями прав.

Для добавления пользователя в существующую роль используется команда sp_addrolemember. Эта команда позволяет назначить пользователя роли, что автоматически даёт ему все права, связанные с этой ролью. Пример синтаксиса:

EXEC sp_addrolemember 'роль', 'пользователь';

Например, чтобы добавить пользователя JohnDoe в роль db_datareader, выполните следующую команду:

EXEC sp_addrolemember 'db_datareader', 'JohnDoe';

Важно, что для выполнения этой операции необходимо иметь права администратора базы данных или быть владельцем базы данных.

Для проверки, была ли успешно выполнена операция, можно использовать команду sp_helprolemember, которая выведет список пользователей, состоящих в заданной роли:

EXEC sp_helprolemember 'db_datareader';

Если требуется удалить пользователя из роли, используется команда sp_droprolemember, которая выполняет обратную операцию:

EXEC sp_droprolemember 'db_datareader', 'JohnDoe';

Для более гибкой настройки доступа можно использовать роли на уровне схемы, а не всей базы данных. В этом случае нужно использовать команды ALTER ROLE и ADD MEMBER для назначения пользователя к схеме или конкретной группе прав. Это позволяет более детально управлять доступом в зависимости от задач.

Проверка доступа пользователя к объектам базы данных

Пример запроса для проверки разрешений пользователя на конкретные объекты базы данных:

SELECT
dp.class_desc AS ObjectType,
OBJECT_NAME(major_id) AS ObjectName,
dp.permission_name AS Permission,
dp.state_desc AS PermissionState
FROM
sys.database_permissions dp
WHERE
dp.grantee_principal_id = USER_ID('имя_пользователя');

Этот запрос вернет список объектов (таблиц, представлений и др.), к которым у пользователя есть доступ, а также типы разрешений (например, SELECT, INSERT, UPDATE) и их состояние (GRANT, DENY).

Другим способом проверки является использование функции HAS_PERMS_BY_NAME, которая позволяет определить, имеет ли пользователь конкретное разрешение на объект:

SELECT
HAS_PERMS_BY_NAME('имя_объекта', 'OBJECT', 'SELECT') AS CanSelect;

Функция вернет 1, если разрешение на SELECT есть, и 0 – если нет.

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

Также рекомендуется использовать представление sys.syslogins для проверки общих прав входа пользователя в систему SQL Server, что может быть полезно для диагностики проблем с доступом на уровне сервера.

Для более глубокого анализа можно использовать инструмент SQL Server Management Studio (SSMS), который позволяет визуально проверить права пользователей на объекты через свойства базы данных и вкладку «Безопасность» в интерфейсе.

Удаление пользователя и логина из SQL Server

Удаление пользователя и логина из SQL Server осуществляется в два этапа: сначала необходимо удалить самого пользователя из базы данных, а затем удалить логин, ассоциированный с этим пользователем, из сервера.

Для удаления пользователя из базы данных выполните команду:

DROP USER [имя_пользователя];

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

ALTER AUTHORIZATION ON SCHEMA::[имя_схемы] TO [новый_владелец];

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

DROP LOGIN [имя_логина];

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

SELECT * FROM sys.syslogins WHERE name = 'имя_логина';

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

Вопрос-ответ: