1

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?

1 Answer 1

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.