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