删除权限后，用户AD帐户仍可访问数据库 [英] User AD account can still access database after remove permissions

问题描述

所以我们有一个奇怪的问题，我在Windows 2012 Server 64位操作系统上运行SQL Server 2012的SQL服务器实例之一上删除了除PUBLIC以外的AD用户帐户的数据库级别访问权限。

So we have a weird issue in that I removed database level access except for PUBLIC for an AD user account on one of our SQL server instances running SQL Server 2012 on Windows 2012 Server 64 bit OS.

但是用户仍然可以查询数据库！我从SSMS和T-SQL检查了GUI上的设置，但它没有显示访问权限。

BUT the user can still query the database! I checked the settings on the GUI from SSMS as well as from T-SQL and it does not show access.

任何想法为什么用户仍然可以访问数据库并在删除权限后查询它来自登录帐户？

Any ideas why the user can still access the database and query it after removing permissions from the login account?

推荐答案

据推测，该用户是具有权限的AD组的成员。

Presumably the user is member of an AD group that has permission.

在相关数据库中运行此命令：

Run this in the database in question:

EXECUTE AS LOGIN ='DOMAIN\user'

go

SELECT * FROM sys.login_token

SELECT * FROM sys.user_token

go

REVERT

EXECUTE AS LOGIN = 'DOMAIN\user'
go
SELECT * FROM sys.login_token
SELECT * FROM sys.user_token
go
REVERT

这将列出用户所属的所有AD组等。然后，您需要检查哪些内容可以访问。

This will list all AD groups etc the user is a member of. Then you need to check which of these that give access.

这篇关于删除权限后，用户AD帐户仍可访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！