Changing the owner of a schema removes all direct permissions of objects owned by the schema.

,

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

5 (1)

Share

Share

Rate

5 (1)