Posted by Erland Sommarskog on 12/20/07 22:29

Justin (justinmiyashiro@hotmail.com) writes:
> I have 2 sql server 2000 database that participate in log shipping.
> Server A is the source database, which log ships to a database on
> Server B. The database on Server B is in a warm stand by/read only
> mode. Is there a way that I can add users to the database on Server
> B, without having to add the users to the database in server A? I am
> trying to use the database on Server B as a reporting only database
> and add more users to have permissions to run reports.

No, that is not possible. If you were to make modifications to the
database on server B, then it would not be possible to apply the logs from
server A. What it the changes in the log are not consistent with the
changes you made on server B.

I can think of two options to achieve what you are looking for:

1) Create the logins on both servers, and create the database users on
server A. Then drop the logins on server A to leave the database users
orphaned on server A, but functional on server B. This works best
with Windows logins. And it presumes that the users in question does
not need access to other databases on server A.

2) Create a second database on ServerB with views on all tables in
serverA. Make sure that the second database has the same owner as
the log-ship database. Enable cross-database ownership chaining on
server B, or at least for the two databases. Add the users to
this second database, and grant them SELECT permissions on the views.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]