Azure SQL - Create User scoped to a single database

Question

I originally posted this question on Super User. However, that does not seem like the correct place.

I have an Azure SQL Server that includes one custom database (let's call it my_db). Up to this point, I've been accessing this database with my server admin credentials. However, I want to make it more secure. In an attempt to do this, I wanted to create a login / user that is limited to my_db. I attempted the following:

On the master database, via the server admin credentials

• Created a login using CREATE LOGIN db_login WITH password 'somePassword';

On my other database (my_db), using the server admin credentials

• Created a user using CREATE USER db_login_user FROM LOGIN db_login;
• Grant read, write, and owner permissions via the following statements:
  • EXEC sp_addrolemember 'db_datareader', 'db_login_user'
  • EXEC sp_addrolemember 'db_datawriter', 'db_login_user'
  • EXEC sp_addrolmember 'db_owner', 'db_login_user'

I'm now trying to login via Azure Data Studio. However, I get the following results:

When using db_login_user

Login failed for user 'db_login_user'.

When using db_login

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

I can successfully access the database using the server admin creds. However, once again, I'm trying to make this more secure. At this time, I don't understand why I can't connect to the database using the new user/login. In addition, I don't understand if I should be trying to login with db_login or db_login_user.

How do I create a user with credentials that are limited to a single database on the server?

Thank you!

Answer 1

You should create a Contained database user.

In a contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database.

The concepts are documented here

In your case, you can run the below statements on my_db database

CREATE USER db_login_user WITH PASSWORD = 'enterStrongPassword@1234';
GO

ALTER ROLE db_datareader ADD MEMBER db_login_user;
ALTER ROLE db_datawriter ADD MEMBER db_login_user;
ALTER ROLE db_owner ADD MEMBER db_login_user;

Answer 2

You need to run below query on both the databases (master as well as my_db), and then try with login 'db_login' and password you provided while creating login.

CREATE USER db_login_user FROM LOGIN db_login