Every once in a while one can observe that Logins or Users have been deniedthe 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.

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: Impersonierung funktioniert + kein Verlust an Berechtigungen. 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!

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!

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.

Remember me(Set cookies so I don't need to fill out my details next time) Allow message form(Allow users to contact me through a message form -- Your email will not be revealed!) Notify me of replies

*Antispam:

Bitte geben Sie den Namen dieser Domäne ohne http://www., aber mit Endung ein?Please answer the question above.We ask for this in order to slow down spammers.Sorry for the inconvenience.Please log in to avoid this antispam check.