why can I not query a SQL database in Azure

Question

I am a user access administrator for a SQL Server and SQL Database in Azure and have been unable to give access to my colleague to query the SQL database. Please let me know how I can configure it appropriately.

Answer 1

Just checking in to see if the above answer provided by @Amira Bedhiafi helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

If your SQL Database uses SQL Authentication, you need to create a login for your colleague.

   CREATE LOGIN [username] WITH PASSWORD = 'strong_password';

After creating the login, you need to create a user in the specific database and map it to the login.

   CREATE USER [username] FOR LOGIN [username];

Grant the necessary permissions to the user. For querying, you typically need to grant the SELECT permission.

   GRANT SELECT ON SCHEMA::[schema_name] TO [username];

If you want to grant more permissions, you can use roles like db_datareader:

   ALTER ROLE db_datareader ADD MEMBER [username];

If your SQL Database uses Azure Active Directory (AAD) authentication, you need to add your colleague as a user in the database.

   CREATE USER [******@domain.com] FROM EXTERNAL PROVIDER;

Then, grant the necessary permissions:

   GRANT SELECT ON SCHEMA::[schema_name] TO [******@domain.com];

Answer 3

Follow https://learn.microsoft.com/en-us/answers/questions/1512228/cannot-add-user-to-db-datareader-role-in-database

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin