Take the 2-minute tour ×

I am using SQL server 2008 R2. I have a live database, and a replicated copy of that live database on a different server.

It is using Transactional Publication replication.

When I take our software and point it at the replicated copy it doesn't work. At first it seemed like the database didn't have the correct logins under security.

BUT - that doesn't seem to be the problem. It is more likely that the database is locked down somehow because of the replication? Can someone please point me to some info to confirm that.

In management studio on the server when I am logged in as a local administrator, when I click one of the replicated databases it just gives me the error,

'The database blah is not accessible'

How can I check that the replication is working? Will I be able to back up the database and restore it somewhere else, any other suggestions welcome.

share|improve this question
permissions?.... –  Mitch Wheat Nov 29 '11 at 1:37
yeah I think it might be permissions, but I can connect to the original databases using windows authentication from my machine. The replicated databases are taking the permissions with them when they are replicating? And in the security list it looks right. –  peter Nov 29 '11 at 1:43
The answer to the title is "Yes', but that doesn't solve your problem. Per the Transactional Replication docs on MSDN, not only can you access a subscriber database in read-only mode, but you can even set up a subscriber that will accept updates - msdn.microsoft.com/en-us/library/ms151176.aspx –  Marc Bollinger Nov 29 '11 at 1:44
Thanks. It must be a permissions issue. –  peter Nov 29 '11 at 1:47
add comment

migrated from stackoverflow.com Nov 29 '11 at 5:32

This question came from our site for professional and enthusiast programmers.

2 Answers

Security isn't part of SQL Replication. That has to be done manually.

share|improve this answer
add comment
up vote 1 down vote accepted

Turns out to be a permissions error. The permissions looked right from a database server point of view (Security -> Logins). These permissions as far as I can tell were moved over as part of the replication.

But when I looked at the individual databases the security under them was missing (database -> Security -> Users).

Under the database security the only users were dbo, guest, INFORMATION_SCHEMA, and sys.

share|improve this answer
add comment

Your Answer


