Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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
1  
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

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

2 Answers

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
up vote 1 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.