If you have ever used database projects with SSDT I’m sure you’ve experienced a situation where that user you just created yesterday is suddenly complaining to you that they can no longer access the database. Often this is because SSDT deployment can purge permissions if they aren’t in the project, either because the option is selected to drop these users or because the database was completely recreated and the users were never put back because they weren’t in the project.
So, there becomes a need to associate database or even server level permissions to your projects and this is where things get tricky. Not all companies have highly skilled DBAs doing 100% of the database development for these projects. Inevitably, you end up with logins and users being put into a project or possibly even a manually written script and then deployed to various environments.
Very often I find myself fixing a very common problem where the database level user’s connect rights are revoked immediately after creation. This confuses many but happens for a very good reason and can be solved with simple education.
First we’ll go over what the problem looks like. You attempt a connection via SSMS and receive one of these two messages:
Msg 916, Level 14, State 1, Line 1
The server principal “loginName” is not able to access the database “sqlHammer_Test” under the current security context.
As you can see the user in question here is DOMAIN\qasqlservice. The down arrow to me says, “disabled,” but technically there is not a disabled setting for the user. Rather, in this case the CONNECT permission was not granted.
Normally the way that this happens is that a server level login is created from Windows. Then the stored procedure sp_addrolemember is executed to make that login a member of a database level role. The problem is that the database level user was never created. So, the stored procedure noticed that it doesn’t have a database level user and knows that it needs one in order to make it a member of a database level role. The stored procedure then creates the user so that it can move forward but, in the interest of security by design, it will not grant CONNECT permissions to that user because you did not explicitly tell it to open that access path.
This does not occur, however, for SQL accounts. On my test server I created a SQL account named, Hammer. I then ran sp_addrolemember and received this message.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35
User or role ‘Hammer’ does not exist in this database.
This is the error that I’d like to see on domain accounts also. I have not found anything documented on why it acts differently for domain accounts but I believe it has to do with the SIDs. Database users that have domain style names (domain\user) can be created without using the FOR LOGIN option but they end up with the same SID as the server level login. SQL accounts that don’t use the FOR LOGIN option will have a newly generated SID. Since the sp_addrolemember stored procedure doesn’t know what login you might want your user associated to it will fail for the SQL account while accepting the domain account.
The fix for this problem is simple enough. Running the below script or dropping and recreating the database user will solve the problem.
USE [sqlHammer_Test] GRANT CONNECT TO [DOMAIN\qasqlservice]