Sql-server – Can changing database owner affect permissions or role memberships

sql server

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.

Best 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.

Related Solutions

Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

to relink the login to the correct id you will have to run (I assume that the login is appuser, and the databas user appuser):

alter user appuser with login = appuser ;

From BOL:

To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema. Requires CONTROL permission on the database to remap a user to a login

As you explained that you have the db_owner role on the database, it should work.

Sql-server – Error on changing ownership of database from Files Page in database properties dialog box

The database owner is the login in sys.server_principals that owns the database, as defined by its SID. After a restore it may easily be the case that the login that was used to restore the database is not the login that was the previous owner of the database. (This is even more likely to happen when the database if moved between servers.)

So, there could be three settings that you are having trouble with

The database owner_sid is not equal to the database's db_owner sid. You can compare this for database ABC by:

SELECT owner_sid FROM sys.databases where name = 'ABC';

SELECT sid from ABC.sys.database_principals WHERE name = 'dbo';
Next you are getting a message saying that your proposed database owner_sid also exists as a user in sys.database_principals. We know this from the error message about mapping the proposed owner.

So your steps are:

 USE ABC;
 DROP USER James;
 ALTER AUTHORIZATION ON DATABASE::ABC TO [James-PC\James];

You dropped the user which is not needed, since you are making it the dbo. Once that is out of the way, then your login can be made the owner of the database.

EDIT: You can also use SSMS to delete the James user from ABC database. Then you could return to the Database properties File tab to set the database owner. (Sorry, I tend to think scripts.)

Best Answer

Related Solutions

Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

Sql-server – Error on changing ownership of database from Files Page in database properties dialog box

Related Question