All we need is an easy explanation of the problem, so here it is.
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'
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)
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.
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
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 🙂