User AD account can still access database after remove permissions RRS feed

  • Question

  • 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.

    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?

    Thursday, May 23, 2019 5:24 PM

All replies

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

    Run this in the database in question:

    SELECT * FROM sys.login_token
    SELECT * FROM sys.user_token

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

    Erland Sommarskog, SQL Server MVP,

    • Proposed as answer by Puzzle_Chen Friday, May 24, 2019 5:52 AM
    Thursday, May 23, 2019 9:37 PM