0

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

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];
  • Ahhhhhh, thanks so much! I knew it was something basic. – ThatDataGuy Jan 28 at 15:16

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.