Changing the owner of a schema removes all direct permissions of objects owned by the schema.1
January 22, 2020 by Kenneth Fisher
This is one of those things that probably doesn’t happen all that often, but bit me in the … elbow … to the tune of ~10 hours recently.
CREATE SCHEMA SchemaA CREATE TABLE Table1 (col1 int); GO CREATE USER UserA WITHOUT LOGIN; CREATE USER UserB WITHOUT LOGIN; GRANT SELECT ON SchemaA.Table1 TO UserB; GO EXEC sp_DBPermissions NULL, 'UserB' GO
You’ll notice that UserB does in fact have SELECT permissions on SchemaA.Table1. But now let’s change the owner of SchemaA.
ALTER AUTHORIZATION ON SCHEMA::SchemaA TO UserA; GO EXEC sp_DBPermissions NULL, 'UserB'
Poof! Permissions gone!
--Cleanup DROP USER UserB; DROP TABLE SchemaA.Table1; DROP SCHEMA SchemaA; DROP USER UserA;
For those of you that like documentation you can find it in the alter authorization BOL. Look near the end of the section and you’ll see this paragraph:
If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.
And any object(s) within the schema appear to be considered part of the target as far as this rule goes.
I’ll be honest this probably isn’t a huge issue for most people. Not a lot of people use multiple schemas, even less change their owners, and on top of that most people (that I’ve seen) use database level permissions (db_datareader, EXECUTE at the db level etc). Which as I’ve said, makes this an uncommon issue. But still one that you should keep in the back of your mind in case it comes up and bites your … elbow.
Category: Microsoft SQL Server, Security, SQLServerPedia Syndication | Tags: Microsoft SQL Server, security
One thought on “Changing the owner of a schema removes all direct permissions of objects owned by the schema.”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Something unusual. For example did you know that if you change the owner of a schema any permissions for the objects in the schema disappear? […]