1

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
5
  • Have you tried through "MSTSC" that is Remote Desktop login with your 'SA' or 'User' Login. Feb 26, 2015 at 12:16
  • I don't understand what I'll acheive with that. I don't need to RDP to this server, I can connect to this server with my local ssms Also I'm a sysadmin so I don't need to use sa Feb 26, 2015 at 12:18
  • Out of Nine "Server Role" permission. If you have "sysadmin" permission then you have all permission of DB as well as Server. Have you check out your Server DB Permission. Feb 26, 2015 at 12:34
  • For reference you check out here msdn.microsoft.com/en-us/library/ms191291.aspx Feb 26, 2015 at 12:40
  • Ok I've edited my question, I appreciate that you are trying to help but help like - check sysadmin role permission is not helpful, please read my question, I think that it is clear that I'm aware of what sysadmin role means Feb 26, 2015 at 12:47

2 Answers 2

0

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;
9
  • Yes it worked(actually I've been looking for this type of query didn't know this was possible - thanks!!) - it returns public server role and group role that I wrote, so nothing new there Feb 26, 2015 at 13:01
  • So basically on this server the AD group that should grant sysadmin is missing from the SQL login token, right? What about at OS, from a command prompt on this problem machines run whoami /all or whoami /groups, see if the mystery AD group is present or not. Feb 26, 2015 at 13:07
  • Not familiar with cmd on that level - actually no, the thing is that group exists on that server, but it has no real permissions(not that I could find) there, and still a member of that group can do dmls and shrink(examples), and I cannot find how is he able to do that Feb 26, 2015 at 13:20
  • Is the entry in login_token marked as GRANT OR DENY or DENY ONLY? Feb 26, 2015 at 13:21
  • an what about select * from sys.fn_my_permissions(NULL, 'server'), what does it list, when logged in as the trouble AD group member? Feb 26, 2015 at 13:24
0

Finally I've found the source of those permissions:
This AD user had an "orphan" user in database with higher permissions than AD group.
So in order to connect to instance this user needs to be a part of the group
But after the connection he can get permissions directly from his "orphan" user in DBs
Even if the group that he is a part of is only public group
This group don't even have to have a corresponding user in database.
Hope I made myself clear because I wasn't familiar with this issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.