1

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?

8
  • Are you doing it using script or from user properties? and what's your SQL Server version?
    – Ronaldo
    May 21 '20 at 15:07
  • @Ronaldo I have tried it both ways but I get permission error either way. I am using SSMS v18.4.
    – Alex
    May 21 '20 at 15:14
  • I meant the version of SQL Server (select @@version;). To add a member to a database role you need any of these permissions. Also, if you run select is_member('db_owner');, what's the result? The doc says: Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database
    – Ronaldo
    May 21 '20 at 17:48
  • @Ronaldo SQL Server 2012. The result of the second query is 0, there are no members of the db_owner fixed role. I am trying to allocate someone to that role but unable to.
    – Alex
    May 21 '20 at 18:43
  • When you connect to the database and run select user_name() returns dbo right? May 21 '20 at 19:19
0

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

8
  • I can add my Login as a User for the database, and then I can grant full ALTER permissions to that User, including ALTER ANY ROLE. Also, I can grant TAKE OWNERSHIP to that User. Yet I still can't allocate that User to any of the database roles. I don't know why I'd need to impersonate that User given I am actually mapped to that User, I'd just be impersonating myself.
    – Alex
    May 26 '20 at 10:20
  • @Alex, ALTER ROLE still won't grant you privileges to alter a fixed database role, it's in the doc under the Permissions section. The TAKE OWNERSHIP is intended to become the owner of the database and it won't help you add a member to any fixed database role until you are the owner of the database. Were you able to run the ALTER AUTHORIZATION after having the TAKE OWNERSHIP?
    – Ronaldo
    May 26 '20 at 12:18
  • Can you advise to whom I need to ALTER AUTHORIZATION to? Of course, my Windows login is already the owner. If I try to give it to my Login/User, I get the following: An entity of type database cannot be owned by a role, a group, an approle, or by principals mapped to certificates or asymmetric keys.
    – Alex
    May 26 '20 at 17:18
  • @Alex, instead of Login/User it should be Domain\Login. ALTER AUTHORIZATION ON DATABASE::[YourRestoredDatabase] TO [Domain\Login]; Keep the square brackets. First change the ownership to some other login of your DBA team or a local admin like sa, just to see that information change, then you change the ownership back to your login. Just remember that you need the IMPERSONATE ANY LOGIN permission to change the ownership to someone else.
    – Ronaldo
    May 26 '20 at 19:30
  • 1
    I altered to a more secure method then which was to use GRANT IMPERSONATE [dbo] and then EXECUTE AS USER = 'dbo'.
    – Alex
    May 28 '20 at 8:22
0

To solve this, I allowed my User to impersonate the dbo User so that I could then change fixed database roles.

USE [database]
GO
GRANT IMPERSONATE ON User::dbo TO [Domain\Login];

USE [database]
GO
EXECUTE AS User = 'dbo'
GO
ALTER ROLE [db_owner] ADD MEMBER [Domain\Login]
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.