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 YourDatabase SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'User1'