0

I am new to SQL Server authentication methodologies. I am trying to learn to create a read-only user who can access Azure SQL data from Power BI. I have followed the steps and created a login and user as suggested in this post. I am able to login to the database using 'login' and password as suggested here.

My concern is, as I created the user and assigned him the role of the 'db_datareader'; how does this guarantee that I can't modify something in another database(as there can be multiple user associated with login with a different role)? Is there any way I can directly login to the database using username?

huysmania
  • 1,054
  • 5
  • 11
abhi8569
  • 131
  • 1
  • 9

1 Answers1

0

About concerns:

My concern is, as I created the user and assigned him the role of the 'db_datareader'; how does this guarantee that I can't modify something in another database(as there can be multiple user associated with login with a different role)? Is there any way I can directly login to the database using username?

As we know, when we create the read-only user, we need follow bellow steps:

  1. Create the Login in master DB.
  2. Create the read-only user in user DB and mapping to the login.
  3. Alter the 'db_datareader' role to the read-only user.

For Azure SQL database, the Login is used to login the Azure SQL Server and the user to access the database.

We can not alter database role to the login, it must be user level.

One login for one user. If you only grant the 'db_datareader' role to the user, it will be the read-only user for the current database.

For example, if the user "A" is created in database A, it only has the access permission to database A. If you only grant the 'db_datareader' role to the "A", it will be the read-only user.

Ref: https://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Thank you for the response. You say we need login to access the server and user to access the database; But I was able to read data from the database just by using login to get into the server. I never used ‘username’ explicitly. – abhi8569 Jun 25 '20 at 02:29
  • @abhi8569 the user is mapped to the login, ref the statement 'create user for login'. For example, if you are using login A to login the SQL database, it also means you are using the user A. – Leon Yue Jun 25 '20 at 02:37
  • Hi @abhi8569a, usually, you can create the login name same with the username. If my answer is helpful for you, hope you can accept(mark) it as answer. This can be beneficial to other community members. Thank you. – Leon Yue Jun 26 '20 at 01:09
  • Hi @Leon, I was caught up in some other work. Thank you for your explanation. – abhi8569 Jun 30 '20 at 08:23