This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(230.39999999999998, 73%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EN2%3C/text%3E%3C/svg%3E)
SSMS 15.0.18390.0 - SQL Server 2019
From within SSMS, i have just created a local user account (SQL, not domain) in Security/Logins of a server, and gave it Read/Write access to a database. I then went to account properties and disabled the account. When i went to the database/Security/Users, i saw that the user did not have a red "x" on it as if it was enabled. Indeed when i tried an ODBC connection to the server with this user via an application, it worked fine!
Re-enabling and disabling it again had no effect. Since this is a production server, i cannot try restarting it but it seems Disabling a user (from SSMS at least) does not work.
You need to run below T-SQL to disable a database user.
USE MYDB
GO
REVOKE CONNECT FROM User1
Below is a test in my environment.
2... Disable database user Cathy2 mapped to the login Cathy2
Refer to the blog How to disable a database user .
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
There was a bug a long time ago in SSMS which did not disable users from the UI.
Please download the current version of SSMS and retest:
https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15
To see it a login is disabled, run:
SELECT is_disabled FROM sys.server_principals WHERE name = 'thename'
If this returns 0, run
ALTER LOGIN thename DISABLE
I tried to disable from the UI on SSMS 18.10, and that seemed to work. (But I much prefer to work with SQL commands.)
Thank you all for your answers. I now understand better how things work, as a user though, i still consider this a bug.
A disabled account (from the UI) should not be able to do anything. Since it is possible to disable it from the UI, it should be fully disabled. You shouldn't have to execute additional sql statements to prevent it from logging or anything else, otherwise the UI option should not be named "disabled", but "partly-disable" or "disable-some features".
Anyway, thanks again.
So there are two things here:
The first point is definitely suspicious. For a Windows login this could be explained by the Windows users being member of an AD group that has been granted access to SQL Server. But for an SQL login, I can't think of any such "side channel".
The second point on the other hand, is very much by design. Server logins and database users are two somewhat disconnected entities. A database user can map to a login, but it does not have to. This can happen, for instance, if a database is restored from one SQL Server instance to another. So logins and users are administered separately.
There can also be situations where you want a login to be disabled, but the database user to be fully functional. The login may be used for impersonation for implementation of some security features.
Thanks however for the extra clarifications.
To be more specific, I was able to connect to the database from a web page using an ODBC connection string containing this specific account, not login to the server via SSMS.
Are you saying that logins through SSMS did not work?
Well, it may be "by design" as you say but why and how should an SSMS user understand that?
SQL Server is not really designed for SSMS. While SSMS may be convenient interface, SSMS can also hide things. I rarely see such things, because I get information like this by running queries; not by looking in SSMS. Fact is, I find many of the security dialogues in SSMS to be confusing.
Also, in the user account under a specific database, there should also be a way to "enable/disable" the user in that DB.
As Cathy showed, there is such a switch with the CONNECT permission. There is also a similar switch on server level with the CONNECT SQL permission. Although permissions are more complex, since they can come from more than one place and they can also be denied. So the SQL Server team identified the need to have an explicit DISABLE switch on server level, but there is not any similar need on database level.
But if you have a good business case, you can submit your suggestion here: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0.