Disabled Database User?

In my many years of working as a DBA, I have encountered many disabled logins.  However, I have never really encountered what looks to be a disabled database user account.  I didn’t even think it was possible to disable a user account in a SQL Server database.  I checked the user account properties just to makes sure I was correct.  Sure enough, no option to disable a user account. This finally turned out to be a simple case of looks can be deceiving.

When we first received the call that a user was having an issue, we asked what was the error.  This is what we were sent:

For the purpose of this blog, we will be using a SQL login named, testuser.

My first thought was maybe a permissions issue.  I very quickly realized that it was something else.

The first thing I did was look at the properties of the login.  This clearly showed that the login had access to a few databases, except the one related to the reported error, 1GBDB.  As you can see below, it looks like the login doesn’t have access into the database.


However, if you look at the users in the database, you can see that the account does indeed exist in the database.  As you can see below.

Of course the red “x” on the user account was a bit suspicious, we will get into that in a bit.  One of the items I checked was if we have an orphaned account.  So I check the SIDs for the login and the user account using these queries.

SELECT sid, name
FROM sys.syslogins
WHERE name = ‘testuser’

SELECT sid, name
FROM sysusers
WHERE name = ‘testuser’

Here are the results.

As you can see, the SIDs for both the user account and the login are the same. So that rules out an orphaned account.

I still couldn’t figure out why they user couldn’t get into the database.  That red ‘x” was still bothering me.  After some checking, I discovered there was something different about the user account in this database, 1GBDB, when compared to the same user accounts in different databases.

Database Permission properties for the 1GBDB, which the user cannot connect to.

This the a screenshot of the database permissions for the AdventureWorks2014 database, which the user can get to.

There is one important difference, the Connect permissions.  If you remove the “Connect” permissions from a user account in a database that has a corresponding login, you will see this.

Once you grant the Connect permissions for the user account, the red “x” goes away and all should be good.  Unless of course there are other issues.

Thanks for stopping by and I hope you learned something!