Timeline for How does this user log in to the database server, when its login is deactivated on the database server?
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 19 at 4:46 | vote | accept | Yossi Geretz | ||
| Apr 18 at 19:37 | answer | added | Yossi Geretz | timeline score: 0 | |
| Apr 17 at 19:18 | answer | added | David Browne - Microsoft | timeline score: 1 | |
| Apr 17 at 17:59 | comment | added | Yossi Geretz | I updated the question to indicate that I am seeing this behavior even for SQL Server user accounts. Database connections, regardless of account type, are all exhibiting the same behavior we'd expect from containment, yet the database is not contained. (Details above.) | |
| Apr 17 at 17:57 | history | edited | Yossi Geretz | CC BY-SA 4.0 |
Edited to reflect the fact that even SQL Server user accounts to not need a server login in order to connect to the database.
|
| Apr 17 at 16:06 | comment | added | Yossi Geretz | @DanGuzman Thanks! All three databases, master, myDB and myDB_Staging show a containment value of 0. So not explicitly contained but these logins are sure acting like the behavior as described in the literature for a contained database. Strange! | |
| Apr 17 at 14:34 | comment | added | Dan Guzman |
This query will show the database containment: SELECT name, containment FROM sys.databases;. Contained database users and associated permissions are replicated to the secondary so I don't think you can disable on the primary without the secondary too.
|
|
| Apr 17 at 14:09 | comment | added | Yossi Geretz | @sepupic So that's a new one for me. I just looked it up. learn.microsoft.com/en-us/sql/relational-databases/security/… How can I check whether my database is contained in this manner? | |
| Apr 17 at 13:39 | comment | added | Yossi Geretz | @Craig Maybe I misunderstood your question? Are you suggesting I execute REVOKE CONNECT FROM [********@hotmail.com] or are you suggesting something else? | |
| Apr 17 at 13:31 | comment | added | Yossi Geretz | @DanGuzman Yes, the user is in the database. When working with read-replica security, permission to the database needs to be granted on the primary, rather than the read-replica, since the read-replica is read only. That permission flows through to the read replica. The trick is to then deny the user's login access as the SERVER level so that that user is denied access to the primary server, but permitted access to the read-replica server. This works for SQL Server logins but for some reason doesn't work for Azure AD logins. | |
| Apr 17 at 13:13 | comment | added | Yossi Geretz | @Craig But DENY SQL CONNECT would be at the database level, rather than the server. Issuing that to the database on the primary server would flow through to the database on the replica and now that user would not be able to connect to either of those databases. When working with security for a read-replica we have to be working with server level, rather than database level security. Because at the database level, the read-replica is an exact replica of the primary. | |
| Apr 17 at 11:36 | comment | added | Dan Guzman |
Do you see the user in the database under Users?
|
|
| Apr 17 at 9:19 | comment | added | sepupic | >>>account defined as a user on a particular database<<< Maybe you are using contained database? | |
| Apr 17 at 3:56 | comment | added | Craig | I don't really know for sure, but maybe there's something slightly unusual about Entra logins on Azure SQL - perhaps you need to DENY SQL CONNECT? | |
| Apr 16 at 22:59 | history | asked | Yossi Geretz | CC BY-SA 4.0 |