{"id":18,"date":"2013-08-27T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/creating-logins-and-users-why-i-cant-connect"},"modified":"2026-03-18T21:50:04","modified_gmt":"2026-03-18T21:50:04","slug":"creating-logins-and-users-why-i-cant-connect","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2013\/08\/27\/creating-logins-and-users-why-i-cant-connect\/","title":{"rendered":"Creating Logins and Users – Why I can’t connect"},"content":{"rendered":"

If you have ever used database projects with SSDT I\u2019m sure you\u2019ve 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\u2019t 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\u2019t in the project.<\/p>\n

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.<\/p>\n

The Mistake<\/h2>\n

Very often I find myself fixing a very common problem where the database level user\u2019s connect rights are revoked immediately after creation. This confuses many but happens for a very good reason and can be solved with simple education.<\/p>\n

First we\u2019ll go over what the problem looks like. You attempt a connection via SSMS and receive one of these two messages:<\/p>\n

> Msg 916, Level 14, State 1, Line 1
\n>
\n>
\n> The server principal \u201cloginName\u201d is not able to access the database \u201csqlHammer_Test\u201d under the current security context.<\/p>\n

\"\"<\/a>Once you see these errors the troubleshooting begins and you\u2019ll notice your user in the database has a down arrow in dark red, as seen below.<\/p>\n

\"\"<\/a><\/p>\n

As you can see the user in question here is DOMAIN\\qasqlservice. The down arrow to me says, \u201cdisabled,\u201d but technically there is not a disabled setting for the user. Rather, in this case the CONNECT permission was not granted.<\/p>\n

\"\"<\/a><\/p>\n

What happened?<\/h2>\n

Normally the way that this happens is that a server level login is created from Windows. Then the stored procedure sp_addrolemember<\/a>\u00a0is 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\u2019t 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.<\/p>\n

This does not occur, however, for SQL accounts. On my test server I created a SQL account named, Hammer. I then ran sp_addrolemember<\/a>\u00a0and received this message.<\/p>\n

> Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 35
\n>
\n>
\n> User or role \u2018Hammer\u2019 does not exist in this database.<\/p>\n

This is the error that I\u2019d 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\u2019t use the FOR LOGIN option will have a newly generated SID. Since the sp_addrolemember<\/a>\u00a0stored procedure doesn\u2019t know what login you might want your user associated to it will fail for the SQL account while accepting the domain account.<\/p>\n

The solution<\/h2>\n

The fix for this problem is simple enough. Running the below script or dropping and recreating the database user will solve the problem.<\/p>\n

> USE [sqlHammer_Test]
\n>
\n>
\n> GRANT CONNECT TO [DOMAIN\\qasqlservice]<\/p>\n","protected":false},"excerpt":{"rendered":"

If you have ever used database projects with SSDT I\u2019m sure you\u2019ve 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\u2019t in the project, either because the option is […]<\/p>\n","protected":false},"author":1,"featured_media":519,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-18","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/18","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=18"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/18\/revisions"}],"predecessor-version":[{"id":281,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/18\/revisions\/281"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/519"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=18"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=18"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=18"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}