Is there a way to create a second login (with user) in the Azure PaaS database?

Question

We have moved our database from a physical server to a Azure PaaS SQL database. The server login is used by the application to connect to the database. I need to create another login account with read-only access to the database. Can someone please help.

Things i have tried already.

CREATE LOGIN login123
WITH PASSWORD = ******* 

CREATE USER login123 
FOR LOGIN login123
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER login123

The above was executed successfully but when the application uses this login it gets the below error. "The server pricipal "login123" is not able to access the database "master" under the current security context. Cannot open user default database. Login failed."

Answer 1

When you run this query:

CREATE LOGIN login123
WITH PASSWORD = ******* 

CREATE USER login123 
FOR LOGIN login123
WITH DEFAULT_SCHEMA = dbo

ALTER ROLE db_datareader ADD MEMBER login123

This means that the new user only have the readonly permission for the database.

Which database the query run in, the readonly permission is for which database.

The user don't have the permission to access other database or master db.

For more details, please see:

1. Controlling and granting database access to SQL Database and SQL Data Warehouse
2. Database-Level Roles

If you want the user both have the readonly permission to more database, you should create more user(with the same) in different database. Using one Login mapping to more users.

Here the T-SQL code, I tested and it works in Azure SQL database:

USE master
CREATE LOGIN login123
WITH PASSWORD = '****' 
GO
CREATE USER login123 
FOR LOGIN login123
WITH DEFAULT_SCHEMA = db_datareader
GO


USE Mydatabase  
CREATE USER login123 
FOR LOGIN login123
WITH DEFAULT_SCHEMA = dbo
GO
ALTER ROLE db_datareader ADD MEMBER login123
GO

``````

Hope this helps.