Sql-server – Finding AD group permissions source

permissionssql serversql-server-2012

We have an AD group as a Login in several servers, usually as sysadmin,
but I've found one server that this login has no access to any DB and has a server public role only.
I've talked to a member of this group about this issue,
and he told me that he has an access to this Server's DBs.
And endeed by opening SSMS as different user(using his AD credentials) at my local station,
he could select, update tables in this server, even shrinking a log file(but cannot see logins except sa and this group).
I cannot find a source for those permissions:
There is no Login\User with his credentials,
there are other AD groups, but if I revoke connect from this group then
he cannot longer connect to this server.
Public server role has only view any database permission
Public DB role has no permissions on dbo schema
Database is not in partial containment….
Where else can I search for a source of this Login's permissions?
*SQL Server 2012 on my and server's computers.

I used this query to fing this login

   USE [master]
GO

DECLARE @username sysname
DECLARE @objname sysname
DECLARE @found integer
DECLARE @sql nvarchar(4000)
DECLARE @results TABLE (Login sysname)

SET @username = ' '
WHILE @username IS NOT NULL
BEGIN
SELECT @username = MIN(name)
FROM master.dbo.syslogins WITH (NOLOCK)
WHERE sysadmin = 0
AND securityadmin = 0
AND serveradmin = 0
AND setupadmin = 0
AND processadmin = 0
AND diskadmin = 0
AND dbcreator = 0
AND bulkadmin = 0
AND name > @username
AND Name NOT LIKE N'NT %'
AND Name NOT LIKE N'##%'
-- this is the list of non system logins
-- ids in server roles may not have corresponding users
-- any database but they should not be removed
SET  @found = 0

IF @username IS NOT NULL
    BEGIN
    --  now we search through each non system database
    --  to see if this login has database access
    SET @objname = ''
    WHILE @objname IS NOT NULL
    BEGIN
        SELECT @objname = MIN( name )
        FROM master.dbo.sysdatabases WITH (NOLOCK)
        WHERE
        name > @objname
        AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

        IF @objname IS NOT NULL
        BEGIN
            SET @sql = N'SELECT @found = COUNT(*) FROM [' + @objname
            + N'].dbo.sysusers s WITH (NOLOCK) JOIN master.dbo.syslogins x WITH (NOLOCK)
            ON s.sid = x.sid WHERE hasdbaccess = 1 AND x.name = '''+ @username + ''''
            EXEC sp_executesql @sql,N'@found Int OUTPUT',@found OUTPUT
            --SELECT @found, @objname, @username
            IF @found IS NOT NULL AND @found > 0
                SET @objname = 'zzzzz'  -- terminate as a corresponding user has been found
        END
    END

    IF @found = 0
    BEGIN
    INSERT INTO @results
    SELECT @username
    END
END
END

SELECT SERVERPROPERTY('ServerName') ServerName ,Login
FROM @results r
ORDER BY Login
GO

Best Answer

Login as the mystery user. Then run:

select * from sys.login_token

Somewhere in the output list will be token that grants you access. For instance you can try this (not guaranteed to work):

select * 
from sys.login_token lt
join sys.server_principals sp on sp.sid = lt.sid;