This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Can changing database owner affect permissions or role memberships?
Question
A couple years ago we ran into a situation where we were changing the database owner from a DBA’s login to sa, and immediately thereafter we received a complaint that the service login (which was NOT the DBA’s login) no longer had access to the database. On checking, the service login had indeed lost it’s role memberships.
We only had 3 DBAs at the time, and nobody else had access to make a role membership change, so I’m pretty sure that someone did not specifically remove the service login from the roles. However, I have not seen any warnings on articles about changing owner about something like this occurring, so I’ve not been able to explain it, and we couldn’t reproduce the issue.
So, for example, the database owner was changed from DOMAIN\jdoe, and Windows login “DOMAIN\appuser” mysteriously lost it’s built-in role membership.
Is there a situation where changing the owner of the database would cause permission or role changes?
Note that this question pertains to changing the actual database owner, NOT removing a login from the db_owner role.
asked 2020-03-30 by Tony Hinkle
Answer
If the user is the owner of the database, that user implicitly gets db_owner permission, without having to be explicitly assigned that role.
The owner of the database (a single user) is a different assignment than membership in the db_owner role, but have the same effect. I like to remember it as the owner of the database is an INSTANCE LEVEL setting (ie, it’s in sys.databases, but db_owner role membership is a DATABASE LEVEL permission.
A user can be ONLY the (server level) database owner, and not have any permissions within that database. When that happens, changing the database owner to sa would effectively lock that user out of the database they previously owned. This would not revoke permissions from the user, but it would change the effective permissions.
To avoid this problem, you would need to add the user to the db_owner role for that database first, then change the owner.
In the context of your question, if the service account was the prior database owner, then this would explain a change in effective permissions, even though there were no changes to the explicit database-level permission/role assignments.
answered 2020-03-30 by Andy Mallon