I have log shipped a database from one SQL Server 2008 R2 server to another in standby mode for reporting purposes. Permissions are granted via a Windows AD Group called
DOMAIN\DB Access Group which is both a login and a user in the primary database. The login was never created in the secondary database, yet people are reporting that they are able to connect and run queries.
Using the SSMS GUI, if I right click on the user properties on the secondary, the server reports that
DOMAIN\DB Access Group is both a user and a login on this server. Checking
sys.database_principals shows the user exists but in
sys.server_principals the login is nowhere to be found. Running
SELECT SUSER_SNAME(SID_OF_AD_GROUP) returns a login name which is nowhere to be found.
Any ideas what might be causing this phantom login behavior?