SQL Server – Can’t Alter Database Roles on Restored Database

restorerolesql serverssms

My SSMS Login (using an AD group) is allocated all server roles apart from sysadmin so I have quite a lot of access.

Using this Login, I have restored a database from its .bak file. However, my Login wasn't mapped to a User before this database was backed-up so when it is restored, I am not mapped to any Users. But, if I go into database Properties > Files, the Owner is me (although it is my personal Windows accounts not my AD Group Login I use within SQL).

I'm not sure how this affects things but it means I can still read/write to all the tables in the database, even though my Login is not a User in this database. I therefore then want to create a User on this database for my AD Group Login which works successfully. But then when I try to allocate this new User to any database roles, it says that I do not have permission.

What steps am I missing? What permissions do I need to allocate database roles to a new User on this restored database?

Best Answer

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

Related Question