Some strange questions about using Windows user groups in SQL Server

Recently, some strange problems have been found during the use of Windows user groups in sqlserver.

SQL Server version: SQL Server 2008 r2

Question 1:Login Failed for user 'xx \ xx'

Operation description:

Add a Windows user to SQL Server and use the user to connect to SQL Server (Windows Authentication). logon fails. the following error occurs in the log:

Login Failed for user 'xx \ xx'. cause:
Token-based server access Verification Failed, resulting in a infrastructure error. Check for previous errors. [Client: <XXXX>]

Check the permission assignment without any exception. Later, the user belongs to a Windows user group, and this user group is also the login of SQL Server, and the connection is explicitly rejected.
SQL permission, try to remove the deny permission on the Windows Group, and find that you can log on normally.

After repeated tests, we found that if a Windows user and the Windows user group it belongs to are both loglogin, the Windows user's permissions are their own permissions + the Windows Group's permissions, permission rejection takes precedence. That is to say, as long as the user group to which a Windows user belongs has the permission to access an object, a Windows user also has this permission. If the user group to which a Windows user belongs has the permission to be denied, if you explicitly grant permissions to a Windows user, the permission is denied. The problem I encountered is that the user's Windows User Group has set a denial of connection SQL
Therefore, no matter whether or not I grant the user the permission to connect to SQL Server, I cannot connect to SQL Server.

 

Question 2:Isolated users ???

Operation description:

This was found during the test of Question 1. After I grant a database permission to a Windows user group, the user to which the user group belongs also obtains this permission (not explicitly authorized to the user ), this has been closed in question 1.

The problem is that after I delete the login (instance-level login, not database-level user) corresponding to the Windows user group, it is found that the Windows user (login in SQL Server) corresponding to this Group still has the permission of the Windows user group.

It seems that you should be careful when deleting the Windows user group, not only to delete login, but also to delete database-level users. Otherwise, security vulnerabilities may occur.

Note:

When a login is deleted, database-level users are not automatically deleted. Such users are called isolated users. (The original article "if the corresponding SQL Server is deleted
Login Name, the database user may become an isolated user ").
Because isolated users do not have login, they cannot generate related logins. Therefore, we generally think that isolated users do not affect security. But for Windows user groups, it seems dangerous.

 

Question 3: orIsolated users ???

Operation description:

On the basis of Question 2, we found an interesting column problem. You can directly use create
User [XX \ XX]
For Login [XX \ XX] statement, which creates a user for the Windows user group at the DB level and can be authorized. This seems to have created isolated users manually.