HOW to Enable / Disable Users (not Logins) in SQL Server 2008

A DBA friend of mine came across a strange problem the other day. He discovered that one of the users in a SQL server database was disabled, thats a database user not a server login. At least we thought it was disabled at first, it behaved as if it was disabled and it had the small red down arrow icon that disabled server logins have, but as we all know there is no way to enable or disable database users in the Management Studio GUI.

I had a quick Google and was quite surprised to discover that lots of people had the same ‘issue’ but most peoples workaround was to delete the user and re-create it.
After some more research, it turns out that the user didnt have CONNECT permissions to the database (which I guess amounts to the same as being disabled).

You can grant CONNECT permissions using the code:

USE YourDatabase GRANT CONNECT TO User1

Or to remove the permissions:

USE YourDatabase REVOKE CONNECT FROM User1

You can check which users in a database have the CONNECT permission by executing this T-SQL:

USE DBName go SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'User1'