Sql-server – Can’t add server user to database role

sql server

I'm trying to create a new user login with sql authentication and also an application role on a specific database. I can create the role and user fine, but when I try to alter the role to add the user as a member I get an error. I must be missing something obvious?

use ciq; 

create role rl_saga_ciq_loader;

grant select on ciq.dbo.ciqcountrygeo to rl_saga_ciq_loader;

CREATE LOGIN usr_saga_ciq_loader_dev WITH PASSWORD=N'ItGoesOnForever!', DEFAULT_DATABASE = ciq;

alter role rl_saga_ciq_loader add member usr_saga_ciq_loader_dev;

EXEC sys.sp_addrolemember @rolename = 'rl_saga_ciq_loader', @membername = 'usr_saga_ciq_loader_dev'

Gives

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35
User or role 'usr_saga_ciq_loader_dev' does not exist in this database.

Best Answer

You missed CREATE USER command after creating the login:

USE ciq;
CREATE USER [usr_saga_ciq_loader_dev] FOR LOGIN [usr_saga_ciq_loader_dev];
Related Question