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.
- Created two logins
- Created two users(
itread) for those logins
- created two database roles
- Created two schemas with names
itand populated some tables
it_read_onlyroles as owners of
- added users
If I try accessing
hr tables as
hrread user, I am able to. However, when I change the owner of
dbo, I am not able to access tables within the
After some time I determined that the role is owner and users are part of that role. So keeping the ownership of
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
- 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