locked
Log shipped database server user SID is created automatically RRS feed

  • Question

  • We have a production server "prod"  which has a user say 'test' that lets the users connect to the application and  a logon trigger which stops them from connecting to the server through SSMS. I  log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping. Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db , after logshipping the new user is replicated in the 'rep' server. This is the weirdest thing I have ever seen , Can some one help please

    Thank you !

    Thursday, June 11, 2015 3:07 PM

Answers

  • Go through all Windows groups that have access to the server, and disable them one at a time and keep them disabled, until the user no longer have access. Then enable all the disabled groups, one at a time. If user gets access, disable again and try the rest. This should give all groups through which they have access.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 12, 2015 8:41 PM

All replies

  • Hi,

    From what you said you have a login named TEST mapped to user named TEST on prod. Now when you LS this DB user TEST moved but ofcourse login did not moved by nature.

    > But people can still login to the 'rep' server and query the 'rep' db

    Do you mean to say users can still login by using TEST login or any other login ?

    By any chance somebody ran script to remove orphaned user and mapped user TEST to login TEST


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, June 11, 2015 4:42 PM
  • ok to clarify 'test' is a group which has all the domain users in it.So everyone is able to login with their windows credentials
    Thursday, June 11, 2015 5:02 PM
  • ok to clarify 'test' is a group which has all the domain users in it.So everyone is able to login with their windows credentials
    Does this login exists on Secondary if so users can definitely use it

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, June 11, 2015 5:19 PM
  • NO It does not and thats mind boggling
    Thursday, June 11, 2015 5:22 PM
  • NO It does not and thats mind boggling

    Are you sure this cannot happen unless this login added as windows login as admin and NT builtin\administrator is there in SQL Server logins

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, June 11, 2015 6:05 PM
  • Yea thats what making me crazy , I cant find any thing thats related to that user in the login
    Thursday, June 11, 2015 9:01 PM
  • xp_logininfo can reveal how come a user has access to a server. When you have Windows users, and there can be groups/roles both in SQL Server and the AD, it can be tricky to find all ways into the server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 11, 2015 10:00 PM
  • Erland,

    I tried that but it dint just give me blank result.

    Thursday, June 11, 2015 11:39 PM
  • Go through all Windows groups that have access to the server, and disable them one at a time and keep them disabled, until the user no longer have access. Then enable all the disabled groups, one at a time. If user gets access, disable again and try the rest. This should give all groups through which they have access.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 12, 2015 8:41 PM