asked
How a Windows user gets access to a database
We have an interesting problem with a user being able to connect to a database when we don't think they should be able to. Here is the setup: - DOM\\UserA is a Windows Active Directory account. - DOM\\UserA is a member of an Active Directory security group DOM\\GroupA. - DOM\\GroupA does NOT exist as a login on the SQL Server (no row in **sys.server_principles** for DOM\GroupA) - DOM\\GroupA DOES exist as a user in the database ClientDB1. - In **sys.database_principals** for this user, type_desc = WINDOWS_GROUP, and the sid is 58 characters long (including the leading 0x). This sid does not exist in **sys.server_principles**. - There is not a row in **sys.server_principles** for DOM\\UserA. - There is not a row in **sys.database_principals** for DOM\\UserA. When we execute this command USE ClientDB1; EXECUTE AS LOGIN = 'DOM\UserA' SELECT * FROM REVERT the command succeeds. I don't understand how the UserA can get access to the ClientDB1 database when the login for DOM\\GroupA has not been added to the SQL logins. Could anyone shed some light on this?
EXECUTE AS doesn't check the connection of the login. That, and the fact that the user can connect, suggests they must be a member of another group that does have access. Because, according to the documentation, even if that login is a member of a group, you can't actually use EXECUTE AS. But, if the user was created in that database using WITHOUT LOGIN, you can. I'd check that.
14 People are following this question.
