question

xnl28 avatar image
xnl28 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?
sql-server-2008-r2securitypermissions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.