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

Leave a comment

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,099 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: