Despite the fact that you're seeing your login as the owner of the database, I don't believe that information is accurate because of the output you got from
select user_name() and
select is_member('db_owner'). I think SQL Server is misleading you. The permission to read/write every table on the database could be explained by the public database role.
About not being able to add a new member to the roles:
Since you're not a member of
db_owner, you won't be able to add a user on a database role. The doc says that:
to change the membership in a fixed database role you need:
- Membership in the db_owner fixed database role
The problem taking the ownership:
When you tried to take ownership of the database (which is a way of being added to the db_owner role) you got the error
Cannot find the principal '[domain login]', because it does not exist
or you do not have permission.
Because by being member of all server roles except
sysadmin, as you described, doesn't grant you the
TAKE OWNERSHIP or
IMPERSONATE permissions and the ALTER AUTHORIZATION for databases doc says those permissions are needed:
Requirements for the person executing the ALTER AUTHORIZATION statement:
If you are not a member of the sysadmin fixed server
role, you must have at least TAKE OWNERSHIP permission on the
database, and must have IMPERSONATE permission on the new owner login.
You can check if you have those permissions from sys.fn_my_permissions running the following queries while connected to your restored database:
SELECT * FROM fn_my_permissions (NULL, 'DATABASE')
WHERE permission_name = 'TAKE OWNERSHIP';
SELECT * FROM fn_my_permissions (NULL, 'SERVER')
WHERE permission_name = 'IMPERSONATE ANY LOGIN';
If no rows are returned, it means you won't be able to alter the owner of the database and to change that you probably need a
sysadmin to execute the
ALTER AUTHORIZATION or grant you enough privileges to do so.
Granting the needed privileges:
GRANT TAKE OWNERSHIP ON DATABASE::YourDatabase TO [YourDomain\YourGroupOrLogin];
GRANT IMPERSONATE ANY LOGIN TO [YourDomain\YourGroupOrLogin];
Note: The login that is going to be the owner of the database cannot be an existent user on that database, or you'll get the following error:
Msg 15110, Level 16, State 1, Line 1
The proposed new database owner
is already a user or aliased in the database.
Further reading: the
Chart of SQL Server Permissions contains all the permissions you get from being a member of fixed roles (server and database level).