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
hr
andit
- Created two users(
hrread
anditread
) for those logins - created two database roles
hr_read_only
andit_read_only
- Created two schemas with names
hr
andit
and populated some tables - Added
hr_read_only
andit_read_only
roles as owners ofhr
andit
schemas, respectively - added users
hrread
anditread
ashr_read_only
andit_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
- 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
Best Answer
In SQL Server, changing the schema owner will drop all permissions. This is a "feature" buried deep in the documentation:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql
For a schema, this apparently applies to the permissions on all objects within it.