Unable to access Azure SQL DB After Creating Login

Question

I have successfully created a login for a user on our Azure SQL DB using the following code:

CREATE LOGIN [xxxxxxxx] WITH PASSWORD=N'4QfaE+AV0TdFBpiP/ZnqCg/clPPwEeT2ZfZzDEdM2k8='

However, when I attempt to access the database with the login I get the error

The server principal "xxxxxxxxxxxxxx" is not able to access the database "xxxxxxx" under the current security context.
Cannot open database "axxxxxxx" requested by the login. The login failed.
Login failed for user 'xxxxxxxxxxx'.

I guess the problem is that I need to give the user permissions to access the database. Give someone snow me how to give full access to the user for the database please.

I am using SSMS 20.2

Answer 1

FWIW, another option is to not create a login in the first place. Skip the login part and just create a user with a password. Since this is SQL Database, containment is enabled, meaning no knob need to be set in order to allow this.

Since we can't USE database in SQL Db anyhow, the connections string need to have the database on it so there shouldn't be any functional disadvantage. (Unless you want to have the same login name and password for other databases on that same logical server, of course.)

CREATE USER CarmenW WITH PASSWORD = 'a8ea v*(Rd##+' 

Answer 2

Create a database user for the login. Connected to the target database run

CREATE USER <UserName> FOR LOGIN <LoginName>

Then grant the user whatever database permissions you need. Eg:

GRANT SELECT TO <UserName>

or

GRANT CONTROL TO <UserName>