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
6 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 17 at 22:37 | comment | added | Yossi Geretz | THANK YOU! That was my mistake. When I was creating the user I was omitting the capitalized clause: create user [[email protected]] FOR LOGIN [[email protected]]. Evidently, when you omit that clause, the User created is a contained user. It has nothing to do with the Login at the Server level. It is irrelevant whether that login exists, is disabled, or does not exist altogether. The authentication takes place exclusively (contained) in the database. If you want to edit your answer to include your explanation for how to create a non-contained user I'll gladly give you the credit for the answer. | |
| Apr 17 at 20:49 | comment | added | David Browne - Microsoft |
Just like in regular SQL Server. Create a login in master, and a user mapped to that login in the target database. In master run create login [[email protected]] from external provider then in your database run create user [[email protected]] for login [[email protected]]. Then if the login doesn't exist, the user is "orphaned" and cannot log in.
|
|
| Apr 17 at 20:23 | comment | added | Yossi Geretz | How do I create a non-contained (i.e. traditional) User which will not authenticate unless a corresponding Login is defined on the Server? And can I do this for an Azure AD account as well as for a SQL Server account? | |
| Apr 17 at 20:11 | comment | added | David Browne - Microsoft | Creating contained database users is optional. | |
| Apr 17 at 20:04 | comment | added | Yossi Geretz | This is opposite behavior from what I am looking for. I specifically want my accounts to authenticate against a Server login so that I can have two copies of the database on different servers, one the primary database, the other is a read-replica, and control authentication at the server level (via Logins) so that users can connect to the database on the replica server, but not connect to the database on the primary server. Since the database (with its defined Users) is EXACTLY the same for the primary and the replica, the only way I can implement this is at the Server level. Is this possible? | |
| Apr 17 at 19:18 | history | answered | David Browne - Microsoft | CC BY-SA 4.0 |