I am trying to understand why changing schema ownership is causing permissions to be revoked.

I am having a hard time understanding schema ownership change. Here are the steps I have taken in order.

  1. Created two logins hr and it
  2. Created two users(hrread and itread) for those logins
  3. created two database roles hr_read_only and it_read_only
  4. Created two schemas with names hr and it and populated some tables
  5. Added hr_read_only and it_read_only roles as owners of hr and it schemas, respectively
  6. added users hrread and itread as hr_read_only and it_read_only roles

If I try accessing hr tables as hrread user, I am able to. However, when I change the owner of hrschema to dbo, I am not able to access tables within the hr schema.

After some time I determined that the role is owner and users are part of that role. So keeping the ownership of hrschema to dbo, I have added securrables to the role hr_read_only (select permissions on objects with hr schema). I am able to access now.

But when I change ownership of hrschema, this is affecting the users too, they were not able to access hr tables, only one way is I have to add back securables again.

Can you please help me understand why changing schema ownership is causing this and any best recommendation to avoid this.

create login itreadonly with password='Password123$'
create login Hrreadonly with password='Password123$'

create user hrread for login hrreadonly
create user itread for login hrreadonly

CREATE ROLE [hr_read_only]
CREATE ROLE [it_read_only]

create schema hr
create schema it

  1. i went ahead and added hrread and itread users part of hr_read_only and it_read_only role.

2.I changed ownerships of hr and it schema to hr_read_only and it_read_only respectively

3.i am able to access now all tables with hrschema when executing as hrread

4.Now i went and changed the ownership of hrschema to DBO

5.I was not able to access now when executing as hrread

6.So i went back to hr_read_only role and added securables giving select permission on tables with hr schema.

7.I was able to access now all hr tables

8.I changed back hrschema ownership to someother role other than hr_read_only and it_read_only and dbo

9.Now i am not able to access any tables with hr schema when trying to execute as hrread,when i tried to see securables for hr_read_only ,i am not seeing anything

I am not able to understand why this is happening,even though i gave securables

  • Is this Microsoft SQL Server? You've tagged the question with sql but "sql" is not a platform, it's a language.
    – Hannah Vernon
    Sep 17, 2014 at 14:31
  • Sorry Max.edited now Sep 17, 2014 at 14:36
  • 2
    Could you post the source code? It might be just me, but I'm finding your description quite tangled and confusing. Sep 17, 2014 at 14:40
  • As far as I can tell you never gave the roles any permissions. When you broke ownership chaining, it failed as per normal. I'm with Mark, need a repro script. Sep 17, 2014 at 14:57
  • 1
    I think i found an answer,When i change hr schema ownership,select permissions are granted to hrread role are lost. Side effect of ownership change: A potentially surprising side effect of changing ownership of an object is that all permissions granted on that object will be lost. As always, it's a good idea to script all permissions granted on an entity before changing its ownership, so that the grants can be re-executed by the new owner, if appropriate. Nov 24, 2014 at 7:01

1 Answer 1


In SQL Server, changing the schema owner will drop all permissions. This is a "feature" buried deep in the documentation:

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.


For a schema, this apparently applies to the permissions on all objects within it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.