Azure Entra Id can login to Azure SQL database server without USER or LOGIN - Stack Overflow

admin管理员组

文章数量:1332889

I have an Entra Id "[email protected]". This Entra Id:

• Can create new Entra Id users;
• Is the Microsoft Entra admin for an Azure SQL database server, which has a few databases;
• Earlier in SSMS I connected to the database engine with that Entra Id.

Whilst logged in as "[email protected]", I have now created a new Entra Id "[email protected]". Just created it, nothing more. I didn't create a USER or LOGIN for that Entra Id in my database server or any databases.

I would expect that if I open SSMS and try to connect to the database engine using "[email protected]", it would deny me.

However, it lets me connect. The grey bar at the top of Object Explorer with the server name then shows me "[email protected]" (even though I connected with "[email protected]").

If I then open a new query window (right click on a database that is not master, "New Query" from context menu), the tab above the query window then shows me "[email protected]".

I am then able to SELECT against a table and even do an UPDATE.

I use SSMS version 19.0.2

I guess SSMS somehow remembers "[email protected]" and uses that instead of "[email protected]". But that is just my speculation.

How do I check whether an Entra Id has access to a database or not?

I have an Entra Id "[email protected]". This Entra Id:

• Can create new Entra Id users;
• Is the Microsoft Entra admin for an Azure SQL database server, which has a few databases;
• Earlier in SSMS I connected to the database engine with that Entra Id.

Whilst logged in as "[email protected]", I have now created a new Entra Id "[email protected]". Just created it, nothing more. I didn't create a USER or LOGIN for that Entra Id in my database server or any databases.

I would expect that if I open SSMS and try to connect to the database engine using "[email protected]", it would deny me.

However, it lets me connect. The grey bar at the top of Object Explorer with the server name then shows me "[email protected]" (even though I connected with "[email protected]").

If I then open a new query window (right click on a database that is not master, "New Query" from context menu), the tab above the query window then shows me "[email protected]".

I am then able to SELECT against a table and even do an UPDATE.

I use SSMS version 19.0.2

I guess SSMS somehow remembers "[email protected]" and uses that instead of "[email protected]". But that is just my speculation.

How do I check whether an Entra Id has access to a database or not?

• azure
• azure-sql-database
• microsoft-entra-id

Share Improve this question Follow asked Nov 21, 2024 at 6:48 user1147862user1147862 4,22688 gold badges3939 silver badges5656 bronze badges 1

• check this link and list all the principal added to your database along with permissions. – Pratik Lad Commented Nov 21, 2024 at 7:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

How do I check whether an Entra Id has access to a database or not?

To check the permissions granted to principal you need to use below query which lists the permissions explicitly granted or denied to database principals.

As per this MS document

SELECT pr.principal_id, pr.name, pr.type_desc,   
  pr.authentication_type_desc, pe.state_desc, pe.permission_name  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
  ON pe.grantee_principal_id = pr.principal_id;

The above query will return you the output like below, if the connect permission is assigned to the user means it can connect to the database:

Sometimes, SSMS caches tokens from previous connections, and use it. You can resolve this by closing and reopening SSMS, or by disconnecting and reconnecting using the correct credentials.

Also check Review any role assignments granted at the server or database level via Azure RBAC (e.g., if [email protected] has been given special permissions) by going your Database >> Access control (IAM) >> Check access >> View my access > search your Entra user if it has any RBAC role assigned.

本文标签： Azure Entra Id can login to Azure SQL database server without USER or LOGINStack Overflow