DISABLE and DENY LOGIN, DENY USER & Effect on Impersonation and Permissions
A short article on the effects – or missing effects – regarding the disabling & denying connect of Logins & Users on impersonation and permission.
Every once in a while one can observe that Logins or Users have been denied the Connect permission or a Login has been disabled.
Therefore a correct expectation and understanding can be critical.
So let’s see a simple demo: We will use the built-in sa-Account, which is used by many as database owner among other (more on that soon in another article – meanwhile I do invite you to still send in data for the survey on that topic), another freshly created Account DeniedLogin and a database called ImpersonateLogin with the according User + another User without Login: SQLUser.
So I am disabling the sa-account as well as the “DeniedLogin”-Account – the latter I also Deny the Connect permission (Remember we “Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.”)
The Database-User “SQLUser” gets denied the Connect permission on the database.
In the GUI the result looks like this:
Now let’s run 4 tests.
So essentially what those queries do, is trying to impersonate the respective Login or User – and proofing success by returning the then respective active role-memberships.
Results: 
DeniedLogin: Impersonation works + No loss of permissions.
In other words: Denying Connect to a Login does not disallow Impersonation. Impersonation is actually another permission which one can use and is not affected even by Disabling the Login! 
Same applies for sa: Impersonation works + No loss of permissions.
In the following test for the User which has been denied the Connect-permission onto the database – and cannot be used as a Login.
Results:
Msg 15517, Level 16, State 1, Line 3
Cannot execute as the database principal because the principal “DeniedLogin” does not exist, this type of principal cannot be impersonated, or you do not have permission.
Msg 916, Level 14, State 1, Line 3
The server principal “S-1-9-3-4049223906-1289824279-1154161590-488313048.” is not able to access the database “ImpersonateLogin” under the current security context.
The GUID does not represent a real server-principal, because the User SQLUser does not have a matching Login. So it tells us, that the users cannot be impersonated inside the database.
The difference for the second user is, that this user only exists inside the database but at the same time has been explicitly denied to connect to it. This has essentially the same result as “disabling” it – just as the guest-user is.
Thereby it is shown that disabling of Logins does not give any security against attacks from inside. And so-called privilege elevation (/-escalation) usually takes part from internal.
Also the old “trick”, to drop the default-database of a Login, is of little help.
For database-users is indeed does have an effect and prevents logon/connect to the respective database – also “from inside”.
Consequentially all permissions (besides the one denied of course) of the respective Login and User stay totally unaffected by and method of deactivation.
This is also true in the context of “external access”-permission for Logins based on asymmetric keys. (Here a forum-thread where the question appeared: “SQL Login “disabled” flag does not work with asymmetric key??”)
ALTER LOGIN is also explained in BOL here: technet.microsoft.com/en-us/library/ms189828.aspx
I hope those things clarified some things and especially recommendations in security-matters.
Happy securing
Andreas
Leave a Reply
Want to join the discussion?Feel free to contribute!