2005 database mirroring

[David]

I have set up a small DB to mirror on a different SQL Server 2005. The
problem is that the following query gives the NULL values for all the columns:

select * from sys.database_mirroring where database_id = db_id('TEST')

Here is more info about the setup. A database "Test" is set as a PRIMARY on
server S1 and is mirrored on the server S2. I can see the results of the
above query in both the databases on servers S1 & S2. HOWEVER, when I fail
over the database such that the TEST database is primary on server S2 then
the above query gives null values on server S1, but I can see the output for
server S2.

Basically, I can see the output of the above query in PRIMARY server only. I
am using the login account that is db_owner on TEST, db_datareader and
db_datawriter on master and msdb databases.

When the login is given sa rights then there is no issue in getting the
output.

Any advice would be greatly appreciated. Thank you.

[Tibor Karaszi]

Perhaps different collation on the two instances and one is case sensitive? What is the database
name? "TEST", "Test" or something else. Also, are there *any* rows in sys.database_mirroring?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"David" <Da...@discussions.microsoft.com> wrote in message
news:7091E317-3648-4DE3...@microsoft.com...

[David]

Well, the database name is "TEST". I took the full backup and transactional
log backup from server S1 and restored it on server S2 with no recovery(as
done in configuring the mirroring).

Yes, I can see the rows when I am logging as sa.

Thanks for your interest Tibor.

[Tibor Karaszi]

> Yes, I can see the rows when I am logging as sa.

I see. I missed the part about permissions. According to BOL, you need below permissions for
sys.database_mirroring:

"To see the row for a database other than master or tempdb, you must either be the database owner or
have at least ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission or CREATE DATABASE
permission in the master database. "

So perhaps it is related to this? I would try to add permissions according to below, until you see
expected result. If you find that the permissions noted in BOL isn't enough, then consider posting
BOL feedback and based on that whether the error is in BOL or a bug/something else.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"David" <Da...@discussions.microsoft.com> wrote in message

news:67294890-4FCD-4CEC...@microsoft.com...

[David]

It didn't work even I gave the login as db_owner for master, msdb, and the
user database. Also, tried setting up the login account for the server role
db_creator.

I can see the meta-data from sys.database_mirroring ONLY when the login
account has 'sa' rights.

Appreciate it Tibor.

[Tibor Karaszi]

It seems like you deliberately avoid using the permissions mentioned in BOL. I suggest you skip the
database roles/server roles and grant the permissions specified in BOL. Based on that then use the
suggestions I posted in earlier reply (BOL feedback/bug report).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"David" <Da...@discussions.microsoft.com> wrote in message

news:8586CF77-C9FB-4F03...@microsoft.com...