Create a Sequence with the calling user as the owner

All we need is an easy explanation of the problem, so here it is.

Background

I have a SQL User that has the following permission:

GRANT CREATE SEQUENCE ON SCHEMA::seq TO SequenceCreator  
GO

I would like to be able to have this user create a sequence that is owned by it. But when I run the following:

EXECUTE AS USER = 'SequenceCreator'
CREATE SEQUENCE seq.Testing START WITH 1 INCREMENT BY 1 NO CACHE 
REVERT

The owner is dbo, inherited from the schema. As shown by the following query:

;with objects_cte as
(    select o.name, o.type_desc,
            case
                when o.principal_id is null then s.principal_id
                else o.principal_id
            end as principal_id
    from    sys.objects o
            INNER join sys.schemas s
                ON o.schema_id = s.schema_id )
select cte.name, dp.name AS owner
from    objects_cte cte
        JOIN sys.database_principals dp
            ON cte.principal_id = dp.principal_id
WHERE  cte.name = 'Testing'

Results:

name            name
Testing         dbo

But when I (as dbo) run this:

ALTER AUTHORIZATION ON seq.[Test--Things] TO SequenceCreator

Then the results of that query change to:

name      owner
Testing   SequenceCreator

But If I drop the sequence and run this:

EXECUTE AS USER = 'SequenceCreator'
CREATE SEQUENCE seq.Testing START WITH 1 INCREMENT BY 1 NO CACHE 
ALTER AUTHORIZATION ON seq.[Testing] TO SequenceCreator
REVERT

I get the following error:

Cannot find the object 'Testing', because it does not exist or you do not have permission.

This is expected because the user SequenceCreator does not the permissions to see much less alter the sequence.

But it seems like, since the user is creating it, it should be able to set who owns it too. (Similar to a CREATE SCHEMA seq AUTHORIZATION dbo call)

Question

How can I get the creating user (SequenceCreator) to own the sequence that it creates without needing a higher level user to transfer the ownership?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

But it seems like, since the user is creating it, it should be able to set who owns it too.

Absolutely not. After creation, the object is owned by the schema owner, and it would be a security hole to allow users to take ownership of DBO’s objects.

How can I get the creating user (SequenceCreator) to own the sequence that it creates without needing a higher level user to transfer the ownership?

Give SequenceCreator ownership of the seq schema.

alter authorization on schema::seq to SequenceCreator

In addition to solving this issue, it’s extremely rare for objects to not be owned by their schema’s owner. Many pepole and tools don’t even know that it’s possible. So it’s best to aviod individual object ownership.

Also it’s generally insecure to allow non-admin users to create objects in a schema owned by dbo. A sequence is probably harmless, but a table, view, or procedure would allow escalation of privilege by the user.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply