0

This fact is documented here:

ALTER AUTHORIZATION (Transact-SQL)

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.

I had no idea about this until I faced the fact.

One day there was a need to give EXECUTE permission on every existing/future stored procedure to some user, this user is a member of db_datareader database role only and he should not be able to modify any data, but since the ownership chain in place once he has an EXECUTE permission he would be able to modify data through the stored procedures. So I had to break the ownership chain and generated a script that changed authorization on all the existing tables to some other user.

As soon as the authorization was changed, all the permissions on all the tables in that database disappeared. I checked the documentation mentioned above and found out that this behaviour is documented. But it's quite unexpected (to me, at least) so I wonder if someone has any explanation. Why did they do so? Why is there the need for dropping all the permissions on some table if the authorization on it was changed?

1 Answer 1

4

Because the new owner don't want to be given an object that a bunch of other people has access to?

Imagine buying a new house. Do you expect other folks to have access to your new house? Or would you say that it is up to you to hand out the keys/codes to your new house - to the people you trust?

It is hard to respond to "why" questions, since we'd have to been in the meeting when this was decided in order to hear the reasoning going back and forth. But above is my guess.

(I thought about this possibly being from the SQL standard, but it doesn't seem like there's an ALTER AUTHORIZATION command in ANSI/ISO SQL...?)

1
  • Thank you Tibor for your answer, this sounds reasonable. The fact is I changed authorization to the user that does not physically access this database, the only scope was to break the chain, so I could not even think your way, the new "house owner" is pretty fake
    – sepupic
    Dec 29, 2020 at 17:00

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.