0

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.

SCRIPT:
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. – Max Vernon Sep 17 '14 at 14:31
  • Sorry Max.edited now – TheGameiswar Sep 17 '14 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. – Mark Broadbent Sep 17 '14 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. – Sean Gallardy Sep 17 '14 at 14:57
  • Sorry,i have added script now. – TheGameiswar Sep 17 '14 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.