Sp adduser: примеры (SQL)

Примеры использования sp_adduser

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

USE MyDatabase;
GO
EXEC sp_adduser 'MyLogin';
GO

Пользователь 'MyLogin' успешно добавлен в базу данных.

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

USE MyDatabase;
GO
EXEC sp_adduser 'MyLogin', 'MyDbUser';
GO

Пользователь 'MyDbUser' успешно добавлен в базу данных.

Создание пользователя и добавление его в роль db_datareader.

USE MyDatabase;
GO
EXEC sp_adduser 'MyLogin', 'MyDbUser', 'db_datareader';
GO

Пользователь 'MyDbUser' успешно добавлен в базу данных и роль db_datareader.

Альтернативные функции в MS SQL

CREATE USER

Инструкция CREATE USER является современной и рекомендуемой заменой, начиная с SQL Server 2005. Она предоставляет больше гибкости, включая создание пользователей на основе сертификатов, асимметричных ключей и пользователей без логина.

sp_grantdbaccess

Хранимая процедура sp_grantdbaccess также устарела, но использовалась в версиях 2000-2005 для аналогичных целей. В отличие от sp_adduser, она не принимает параметр роли.

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

Типичные ошибки

Ошибка при попытке создать пользователя для несуществующего логина.

USE MyDatabase;
GO
EXEC sp_adduser 'NonExistentLogin';
GO

Msg 15410, Level 11, State 1, Procedure sp_adduser, Line 40
Логин 'NonExistentLogin' не существует.

Ошибка при создании пользователя с именем, которое уже существует в базе данных.

USE MyDatabase;
GO
EXEC sp_adduser 'ExistingLogin', 'ExistingUser';
GO

Msg 15023, Level 16, State 1, Procedure sp_adduser, Line 78
Пользователь 'ExistingUser' уже существует в текущей базе данных.

Использование процедуры не в контексте пользовательской базы данных.

USE master;
GO
EXEC sp_adduser 'MyLogin';
GO

Msg 15247, Level 16, State 1, Procedure sp_adduser, Line 22
Пользователям не разрешено выполнять эту процедуру в системных базах данных.

История изменений

Функция sp_adduser была помечена как устаревшая в Microsoft SQL Server 2005. В последующих версиях она сохраняется только для обратной совместимости. Документация не рекомендует её использование в новых разработках. В будущих версиях SQL Server процедура может быть полностью удалена. Все новые сценарии создания пользователей должны использовать инструкцию CREATE USER.

Расширенные примеры использования

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

USE MyDatabase;
GO
DECLARE @login sysname;
DECLARE login_cursor CURSOR FOR
    SELECT name FROM sys.sql_logins WHERE is_disabled = 0;
OPEN login_cursor;
FETCH NEXT FROM login_cursor INTO @login;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC sp_adduser @login;
        PRINT 'Пользователь для логина ' + @login + ' создан.';
    END TRY
    BEGIN CATCH
        PRINT 'Ошибка при создании пользователя для ' + @login + ': ' + ERROR_MESSAGE();
    END CATCH
    FETCH NEXT FROM login_cursor INTO @login;
END;
CLOSE login_cursor;
DEALLOCATE login_cursor;
GO

Пользователь для логина MyLogin1 создан.
Ошибка при создании пользователя для MyLogin2: Пользователь 'MyLogin2' уже существует в текущей базе данных.

Динамическое создание пользователя с проверкой существования логина.

USE MyDatabase;
GO
DECLARE @NewLogin sysname = 'NewServerLogin';
DECLARE @NewUser sysname = 'NewDbUser';
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @NewLogin AND type = 'S')
BEGIN
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @NewUser)
    BEGIN
        EXEC sp_adduser @NewLogin, @NewUser, 'db_datareader';
        PRINT 'Пользователь ' + @NewUser + ' создан и добавлен в роль db_datareader.';
    END
    ELSE
        PRINT 'Пользователь ' + @NewUser + ' уже существует в базе данных.';
END
ELSE
    PRINT 'Логин ' + @NewLogin + ' не существует на сервере.';
GO

Пользователь NewDbUser создан и добавлен в роль db_datareader.

Аналоги функции в других СУБД

MySQL

В MySQL используется команда CREATE USER, а затем GRANT для назначения прав. Пользователь и его права создаются отдельно.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON database.* TO 'newuser'@'localhost';

PostgreSQL

В PostgreSQL также применяется CREATE USER или CREATE ROLE (с опцией LOGIN). Права назначаются отдельно.

CREATE USER newuser WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE mydb TO newuser;

Oracle

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

CREATE USER newuser IDENTIFIED BY password
DEFAULT TABLESPACE users
QUOTA 10M ON users;

Основное отличие от MS SQL заключается в разделении создания пользователя и назначения привилегий, а также в более детальной настройке хранилища в Oracle.