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?

  • 1
    The login exists in prod and it's on the same domain. When you move it to the other server it still exists in the DB but not in the Server. Is that correct? – Ali Razeghi Jun 10 '15 at 23:00
  • I would trace the secondary server to see if the users are actually logging in to that server. – Shawn Melton Jun 11 '15 at 1:25
  • @AliRazeghi Correct, same domain and login exists in primary... in secondary user exists in standby DB but not as a server login. Shawn, users are reporting being able to connect to secondary directly from Excel for example. – Cowlby Jun 11 '15 at 2:14
  • Sounds like you have a SQL Server "Database User" authenticating as the users are in the same domain but doesn't have an associated Server level user. As a result, users are able to connect to the DB. I don't have a lab env. handy to test it out real quick but please try disabling the user from the reporting server and see if that resolves it. – Ali Razeghi Jun 11 '15 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.