Home » Blog » ALTER AUTHORIZATION – Setting the Owner of a Securable

ALTER AUTHORIZATION – Setting the Owner of a Securable

2014-02-28 - General, Security

Introduction

In SQL Server every securable has an owner. The owner is an additional security concept that is separate from the three security hierarchies (securable,privilege and principal).
Similar to the database owner, who has unrestricted access to the database, and everything within, the owner of any securable has unrestricted access to that securable. Let us look at an example.

ALTER AUTHORIZATION Example

First, we need a security principal:

[sql] CREATE LOGIN TestLogin1 WITH PASSWORD='********',CHECK_POLICY=OFF;
CREATE USER TestUser1 FROM LOGIN TestLogin1;
[/sql]

Now let us create a table and confirm that TestUser1 does not have any permissions to it:

[sql] CREATE TABLE dbo.tst(id INT);
GO
EXECUTE AS USER='TestUser1';
SELECT * FROM sys.fn_my_permissions('dbo.tst','OBJECT') AS FMP
ORDER BY FMP.permission_name;
REVERT;
[/sql]

This query first creates a table and then uses sys.fn_my_permissions together with EXECUTE AS to check for current permissions of TestUser1 on the table. The result looks like this:

As expected, TestUser1 does not have any permissions granted on that new table. Now let us make TestUser1 the owner of our table. For that we can use the ALTER AUTHORIZATION statement like this:

[sql] ALTER AUTHORIZATION ON OBJECT::dbo.tst TO TestUser1;
[/sql]

The ALTER AUTHORIZATION statement is built similar to the GRANT statement. It takes a target securable in the same form as the GRANT statement and also a target principal. The only difference is that there is no privilege involved here. You can use ALTER AUTHORIZATION on almost any securable that you can use the GRANT statement on. The securable is required in most cases to be prefixed by the securable class, OBJECT in the above example. Keep in mind that the principal has to be a server principal if the securable is a server scope securable like a SERVER ROLE. You can get the full list of securable classes that you can use ALTER AUTHORIZATION on in its Books Online entry.

Executing the above query and re-checking TestUser1's access will produce this result:

As you can see, TestUser1 now has the CONTROL privilege on the table. The CONTROL privilege always implies all other privileges, giving TestUser1 unrestricted access.

Summary

The ALTER AUTHORIZATION statement can be used to set the owner of a securable. The owner automatically has unrestricted access to the securable, even if no explicit permissions have been granted.