sql permissions on a read only replicated database

Question

Post reply

sql permissions on a read only replicated database

ericb1

SSCrazy

Points: 2354
More actions
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 8:24 am

This was removed by the editor as SPAM

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

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

ericb1 SSCrazy Points: 2354 More actions · Answer 1

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

jeff fisher-291560 SSC-Addicted Points: 419 More actions · Answer 2

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

ericb1 SSCrazy Points: 2354 More actions · Answer 3

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!

ericb1 SSCrazy Points: 2354 More actions · Answer 4

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

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?