Post reply

sql permissions on a read only replicated database

  • November 4, 2014 at 5:05 pm

    #300720

    any help is appreciated! I have 2 sql2012 databases on different servers, production and one for reporting. I do transaction log shipping from one server to another, so the database is replicated on the reporting server, and the SQL accounts applied to the production server is on the replication database, the SID's are even the same. However, when I log into the SQL server with that account, it says the database is unavailable.

    If I use the SQL administrator account, I can access the replicated database just fine. What else am I missing to allow this sql account access to this replicated database?

    thanks!

  • November 5, 2014 at 1:27 am

    This was removed by the editor as SPAM

  • November 5, 2014 at 7:29 am

    #1756756

    thanks for the reply. It is in "Standby / Read-Only" mode

  • November 5, 2014 at 8:24 am

    This was removed by the editor as SPAM

  • November 10, 2014 at 8:32 am

    #1757443

    Log shipping and replication aren't the same thing. Your log shipping machine will have the databases in standby/read-only mode and the database will be unaccessible when it is being updated

  • November 10, 2014 at 8:40 am

    #1757450

    thanks for the reply. Im actually using Commvault to back up my production database, and then using scheduled commvault jobs to restore the logs to my reporting database copy. This seems to work fine, the copy database is completely accessible and queryable when I log into SQL as an administrator. When I log in as another user, it's not. Even though the user is in the production database that is being restored.

    any help is appreciated, thanks!

  • November 18, 2014 at 1:52 pm

    #1759396

    found this article online (http://blogs.technet.com/b/mdegre/archive/2010/08/29/the-server-principal-quot-sqlloginname-quot-is-not-able-to-access-the-database-quot-mydatabasename-quot-under-the-current-security-context.aspx)

    and it seems to point to the sys.syslogins database. When I run this query against a database the user can access correctly, I get the same SID's:

    USE <myDatabaseName>

    SELECT sid FROM sys.sysusers WHERE name = 'sqlLoginName'

    SELECT sid FROM sys.syslogins WHERE name = 'sqlLoginName'

    Results:

    0x045D10D6B4BA8F46BCEC3DCC16B89801

    0x045D10D6B4BA8F46BCEC3DCC16B89801

    but when I run it against the read-only/standby database I get 2 different SID's:

    0xAD950C3EC6B64D489C7EE8DEB8F3B85C

    0x045D10D6B4BA8F46BCEC3DCC16B89801

    So it looks like the user has 2 different SID's, one from the server and one from the replicated database that is being copied over? Is there a way to sync them to be the same? The article above has a command to do this (EXEC sp_change_users_login @Action='update_one', @UserNamePattern='sqlLoginName',@LoginName=' sqlLoginName ';), but it can't alter the read-only database.

    Can I change the SID's on the DR server to match that of the SID on the replicated database?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply