When a login looks fine but cannot log in

Updated 2016-02-09: This also applies when granting database-level permissions, not just roles. Added an example at the end. Also fixed up the final search procedure for servers with case-sensitive collations.

Sometimes after provisioning a login and user it cannot access a database even though everything looks fine, and it's inevitably caused by a missing connect permission on that database. I encountered the issue this week and after having a read of a post by Derek Hammer I wanted to test it for myself also.

As a quick recap of the rules surrounding logins and users.

After you've created a login:
- You can immediate connect and use the master database.
- As there's no user for this login yet a guest user may be used instead. The guest user is enabled in master by default and it has the connect permission explicitly granted by default.
- If there was a connect permission granted to the public role in each database this would also allow access, but this isn't configured by default.
- You may then add the login to some server roles or grant some direct permissions.
After you've created a user:
- The user is granted the connect permission to the current database by default.
- You may then add the user to some database roles or grant direct permissions.

Here's what a normal commission process would look like.

Create Database TestDb
Go
Use TestDb
Go

-- Create the login. This gives you access to maser.
Create 	Login [Test_Account] With Password = 'AKLAN234jn1kj3241235!$#@'
-- Create the user. This gives you access to TestDb.
Create 	User [Test_Account]

-- Check the permissions in TestDb. 
Select  sp.name As login_name,
        dp.name As user_name,
        dpe.state_desc,
        dpe.permission_name,
        dpe.class_desc
From    sys.server_principals sp
Join    sys.database_principals dp
On      sp.sid = dp.sid
Join    sys.database_permissions dpe
On      dp.principal_id = dpe.grantee_principal_id
Where   dp.name = 'Test_Account'

-- Grant further permissions in TestDb.
Exec 	sys.sp_addrolemember 'db_owner', 'Test_Account'

And this is the permission which was granted automatically.

The above login would be able to log in with no problems.

So in attempting to reproduce a missing connect permission I tried mixing up the steps, such as dropping the user and adding the role member, or dropping the login then adding a role member and re-associating them, but I could not make it fail.

In desperation I inspected the definition for sys.sp_addrolemember and found a block of code which looks like it may be particularly relevant:

Select	Object_Definition(Object_Id('sys.sp_addrolemember'))

/*
	    -- ATTEMPT ADDING IMPLICIT ROW FOR NT NAME --
		if @@error <> 0	-- not found
		begin
	        EXEC @ret = sys.sp_MSadduser_implicit_ntlogin @membername
*/

It seems that if you are adding a user to a role and there's a windows login but no user, the user gets created (possibly without a connect permission). So I created a test windows account [VM-WIN81\TestAccount] and ran my test again.

If Exists (Select Top 1 0 From sys.schemas s Where s.name = 'VM-WIN81\Test_Account')
	Drop Schema [VM-WIN81\Test_Account]
If Exists (Select Top 1 0 From sys.server_principals sp Where sp.name = 'VM-WIN81\Test_Account')
	Drop Login [VM-WIN81\Test_Account]
If Exists (Select Top 1 0 From sys.database_principals dp Where dp.name = 'VM-WIN81\Test_Account')
	Drop User [VM-WIN81\Test_Account]

Create 	Login [VM-WIN81\Test_Account] From Windows
Exec 	sys.sp_addrolemember 'db_owner', 'VM-WIN81\Test_Account'

Select	dp.name,
		dp.type_desc
From	sys.database_principals dp
Where	dp.name = 'VM-WIN81\Test_Account'

Select  sp.name As login_name,
        dp.name As user_name,
        dpe.state_desc,
        dpe.permission_name,
        dpe.class_desc
From    sys.server_principals sp
Join    sys.database_principals dp
On      sp.sid = dp.sid
Join    sys.database_permissions dpe
On      dp.principal_id = dpe.grantee_principal_id
Where   dp.name = 'VM-WIN81\Test_Account'

As you can see it created the user but did not grant a connect permission.

Weirdly this isn't just restricted to roles and that procedure. When you grant any direct permission in a database to a Windows account the same thing happens!

If Exists (Select Top 1 0 From sys.schemas s Where s.name = 'VM-WIN81\Test_Account')
	Drop Schema [VM-WIN81\Test_Account]
If Exists (Select Top 1 0 From sys.server_principals sp Where sp.name = 'VM-WIN81\Test_Account')
	Drop Login [VM-WIN81\Test_Account]
If Exists (Select Top 1 0 From sys.database_principals dp Where dp.name = 'VM-WIN81\Test_Account')
	Drop User [VM-WIN81\Test_Account]

Create 	Login [VM-WIN81\Test_Account] From Windows
Grant	Select On sys.tables To [VM-WIN81\Test_Account]

Select	dp.name,
		dp.type_desc
From	sys.database_principals dp
Where	dp.name = 'VM-WIN81\Test_Account'

Select  sp.name As login_name,
        dp.name As user_name,
        dpe.state_desc,
        dpe.permission_name,
        dpe.class_desc
From    sys.server_principals sp
Join    sys.database_principals dp
On      sp.sid = dp.sid
Join    sys.database_permissions dpe
On      dp.principal_id = dpe.grantee_principal_id
Where   dp.name = 'VM-WIN81\Test_Account'

Of course it's easily granted once you've found that this is the cause of your issue.

Grant Connect On Database::TestDb To Test_Account

Researching this entire process was a bit of a wild goose chase that I would not want to go through again, so I created a script to identify accounts like this which I can add to a policy and run on all of my servers each day as a health check. Should an account accidentally end up in this state again I'll be able to do something about it quickly.

Set Nocount On

Declare	@v_DatabaseName Sysname
Declare @v_Sql Nvarchar(4000)

Declare @v_Results Table (
    DatabaseName Sysname,
    LoginName Sysname,
	UserName Sysname
)

Declare CTE_Databases Cursor Forward_Only Local Read_Only Static For
        Select  name
		From	sys.databases d
		Where	d.state_desc = 'ONLINE'
		Order By 1

Open  CTE_Databases
Fetch Next From CTE_Databases Into @v_DatabaseName
While @@Fetch_Status = 0
Begin
		Set		@v_Sql = 'Use ' + Quotename(@v_DatabaseName) + ';

Select  Db_Name(),
		sp.name,
		dp.name
From    sys.server_principals sp
Join    sys.database_principals dp
On      sp.sid = dp.sid
Where   Not Exists (
		Select  Top 1 0
		From    sys.database_permissions dpe
		Where   dp.principal_id = dpe.grantee_principal_id
		And     dpe.state_desc = ''GRANT''
		And     dpe.permission_name = ''CONNECT''
		And     dpe.class_desc = ''DATABASE''
);
'

		Insert	@v_Results
		Exec	sp_executesql @v_Sql

        Fetch Next From CTE_Databases Into @v_DatabaseName
End

Close CTE_Databases
Deallocate CTE_Databases

If Exists (Select Top 1 0 From @v_Results)
Begin
		Select  *
		From    @v_Results
		Order By 1, 2
End

Here's the output:

I should note that Derek says this is part of Microsoft security; but personally I think it's more of a bug and not one that will ever be fixed. It would have been nice if sys.sp_addrolemember printed something to let you know it had created the user for you though.

Share on: