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

12 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:

    Thanks

  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.

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: