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

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

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];