none
User AD account can still access database after remove permissions

    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:

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

    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, esquel@sommarskog.se

    Thursday, May 23, 2019 9:37 PM