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