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
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.