How to add Azure AD user to a Database role

Question

Question: What may be a cause of the error shown below. The user running the code below is a member of db_securityadmin database role:

ALTER SERVER ROLE db_ddladmin ADD MEMBER [myDomainName.com\Juan]

Msg 15151, Level 16, State 1, Line 3 Cannot add the principal 'myDomainName.com\Juan', because it does not exist or you do not have permission.

Remark: User [email protected] does exist and can login to his laptop as [email protected]. The following code also gives the same error:

ALTER SERVER ROLE db_ddladmin ADD MEMBER [[email protected]]

Ref: Add a member to a fixed server role

Answer 1

Before making a user member of Server-level roles or Database-level roles it is important to make sure the login/user is already among the Principals listed on the instance and database.

1. Check if the [email protected] login (or a Windows group of which it is a member) exists on the instance: Security > Logins. If it's not, run a CREATE LOGIN command;
2. Check if there's a user created on the database associated to that login: Databases > YouDataBase > Security > Users. If you can't find one, create it with the CREATE USER command.

A very practical way to verify that in your case is to test if [email protected] can login and navigate to the database you'll be giving permissions using SSMS.