ALTER AUTHORIZATION: примеры (SQL)

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

Пример изменения владельца базы данных. Новым владельцем должен быть участник уровня сервера.

ALTER AUTHORIZATION ON DATABASE::AdventureWorks TO sa;

Команда выполнена успешно.

Пример изменения владельца схемы в текущей базе данных.

ALTER AUTHORIZATION ON SCHEMA::Sales TO [Contoso\User1];

Команда выполнена успешно.

Пример изменения владельца конкретного объекта (таблицы). Класс OBJECT часто можно опустить, если имя уникально.

ALTER AUTHORIZATION ON OBJECT::dbo.Orders TO db_owner;

Команда выполнена успешно.

Изменение владельца асимметричного ключа.

ALTER AUTHORIZATION ON ASYMMETRIC KEY::MyAsymKey TO [MyAppRole];

Команда выполнена успешно.

Похожие функции в MS SQL Server

sp_changedbowner: устаревшая системная хранимая процедура для смены владельца базы данных. В современных версиях рекомендуется использовать ALTER AUTHORIZATION.

sp_changeobjectowner: устаревшая процедура для смены владельца объектов в базе данных. Функциональность заменена ALTER AUTHORIZATION ON OBJECT.

ALTER SCHEMA: используется для передачи объектов (таблиц, представлений) между схемами, а не для смены владельца схемы или объекта. Например, ALTER SCHEMA Sales TRANSFER dbo.Products; перемещает таблицу в другую схему.

Выбор инструмента зависит от задачи: ALTER AUTHORIZATION изменяет владельца, ALTER SCHEMA — принадлежность объекта к схеме.

Аналоги в других СУБД и языках

PostgreSQL: используется команда REASSIGN OWNED для массового перераспределения объектов или ALTER OWNER для конкретного объекта.

ALTER TABLE mytable OWNER TO new_owner;

ALTER TABLE

Oracle: прямой команды нет. Владелец объекта — это схема, в которой он создан. Чтобы "передать" объект, его необходимо повторно создать в другой схеме или использовать экспорт/импорт.

MySQL: концепция владельца объекта на уровне базы данных отсутствует. Привилегии управляются на уровне пользователя и хоста. Для баз данных есть ALTER DATABASE, но не для смены владельца.

SQLite: не поддерживает концепцию владельца объектов или ролевую модель в том же виде.

Sybase ASE: поддерживает команду ALTER AUTHORIZATION, синтаксис схож с MS SQL.

Типичные ошибки при выполнении

Ошибка из-за несуществующего участника (principal).

ALTER AUTHORIZATION ON DATABASE::MyDB TO [NonExistentLogin];

Msg 15151, Level 16, State 1
Cannot find the principal 'NonExistentLogin', because it does not exist or you do not have permission.

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

ALTER AUTHORIZATION ON DATABASE::MyDB TO [SomeDbUser];

Msg 15151, Level 16, State 1
Cannot find the principal 'SomeDbUser', because it does not exist or you do not have permission.

Недостаточные права у текущего пользователя. Для выполнения команды требуются права IMPERSONATE на нового владельца или членство в предопределенных ролях.

-- Выполняет пользователь без прав
ALTER AUTHORIZATION ON SCHEMA::Sales TO [AnotherUser];

Msg 15151, Level 16, State 1
Cannot find the principal 'AnotherUser', because it does not exist or you do not have permission.

История изменений функции

Инструкция ALTER AUTHORIZATION была введена в SQL Server 2005 как замена устаревшим хранимым процедурам sp_changedbowner и sp_changeobjectowner.

В SQL Server 2008 и более поздних версиях расширен список классов сущностей, для которых можно изменить владельца (например, серверные роли, группы доступности).

В актуальных версиях SQL Server команда остается основным способом смены владельца для большинства объектов.

Расширенные примеры применения

Изменение владельца для всех объектов в схеме с помощью динамического SQL.

DECLARE @NewOwner sysname = 'db_owner';
DECLARE @SQL nvarchar(max);

SELECT @SQL = STRING_AGG(
    'ALTER AUTHORIZATION ON OBJECT::' + 
    QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + 
    QUOTENAME(name) + ' TO ' + QUOTENAME(@NewOwner), '; '
)
FROM sys.objects
WHERE schema_id = SCHEMA_ID('Sales') AND type IN ('U', 'V', 'P', 'FN');

EXEC sp_executesql @SQL;

Команды выполняются для каждого объекта.

Смена владельца для конечной точки (Endpoint).

ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO [NT AUTHORITY\SYSTEM];

Команда выполнена успешно.

Изменение владельца сертификата, что может влиять на разрешения для модулей.

ALTER AUTHORIZATION ON CERTIFICATE::MyCertificate TO [CertificateUser];

Команда выполнена успешно.

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

USE master;
GO
ALTER AUTHORIZATION ON DATABASE::RestoredDB TO [Corp\DBA_Login];
GO

Команда выполнена успешно.