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