Over the past few months there have been a handful of times when I had to fix a security problem by granting connect to the user. It wasn’t a big deal so I put it on the ‘figure out later’ list. Today it happened again and I had some time, so I took a deeper look. It turned out to be surprisingly simple. We have a script that calls sp_addrolemember to add a Windows login/group to datareader. It just calls sp_addrolemember, it doesn’t check for the user first and the call to sp_addrolemember always succeeds. The problem is that if you’re passing in a Windows login that exists on the instance it adds it as a user without connect permissions. In a lot of cases the user was already present so running the script just made sure permissions were correct, but in the small number of cases where the user didn’t exist – well, they couldn’t connect!

BOL does call it out clearly:

“If the new member is a Windows-level principal without a corresponding database user, a database user will be created but may not be fully mapped to the login. Always check that the login exists and has access to the database.”

The proc itself is deprecated in favor of ALTER ROLE and so I tested that, it’s cleaner – if the user doesn’t exist the call to alter role fails.