Enable / Disable Users (not Logins) in SQL Server

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

Or to remove the permissions:

USE YourDatabase

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'
Tagged , , , ,

16 thoughts on “Enable / Disable Users (not Logins) in SQL Server

  1. Devesh says:

    Exactly what I was looking for. I found lots of answers for Enabling Logins but none for the users. This is it :). Thanks for posting.

  2. mauriciorpp says:

    Reblogged this on The Lonely DBA and commented:
    Revoke connect is not exactly Disable, but the icons are similar. Too much similar. Read on.

  3. mbrost says:

    Thanks for this 🙂

  4. Suraj says:

    can someone tell me how can a user gets disabled in a DB automatically?

  5. Anonymous says:

    Cudos for the simple solution

  6. ddomanil says:

    cool. helped me out. thanks

  7. Johnny says:

    Great. Helped me too. Thanks

  8. Raman says:


  9. ZeeSpree says:

    Thanks. But what causes this ?
    Someone mapped this login to this database but never gave any access but public ? or someone exclusively revoked the CONNECT ?

  10. Vic says:

    I had this error when I restored a db, granted execute to a proc to a user; and the login was created prior to the restore. When I looked at the restored db, the user showed disabled; and the server login could not be granted access (according to the SSMS, no change had occurred). However, granting CONNECT solved the disabled bit.. Thanks!

  11. Anonymous says:

    Thanks for posting.

  12. JRStern says:

    Ditto thanks. Presume this works on Azure too.

  13. Graham Like says:

    I had this problem, caused by me granting rights to a table to a user before I’d created the user. Ie GRANT SELECT ON dbo.[ViewName] TO [domain\ad_group_name] before I’d run: CREATE USER [domain\ad_group_name]. Everything ‘looked’ good apart from the little down arrow next to the user in the Security\Users folder and the user couldn’t access the table. GRANT CONNECT TO [domain\ad_group_name] removed the down arrow and gave the user permission.

  14. Thank you for posting this! I was fumbling around this problem and Google brought me here.

  15. Anonymous says:


  16. 90358 says:

    Hi All ,

    can some one help, How to enable user in databases.

    I have 5000 DB’s in instance

    This is for one database .

    USE YourDatabase

    Need query for N no. of databases

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: