Permissions for sys.database_mirroring in SQL Server

Question

I need to assign permission to use sys.database_mirroring and of course I want to give the minimum amount of privilege. When I looked at microsofts homepage it says:

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. To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role.

After trying on a test server I can't figure out what the last sentence mean. Anyone know in which cases sysadmin privilege is needed?

Answer 1

The comment: "To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role" is found in the discussion of sys.database_mirroring dynamic management view at:

https://msdn.microsoft.com/en-us/library/ms178655.aspx

Try running the following statement:

select * from sys.database_mirroring 

Using my sysadmin account, I get a row for every database. Most rows are all NULL because they are not mirrored databases, but the two mirrors report their status. If you only want to check running mirrors you could use:

select * from sys.database_mirroring 
where mirroring_guid is not null

Using my non-sysadmin account, I get nothing. No rows returned, no ROWCOUNT, et cetera, just a delayed 'ding' sound.

So, that note is just trying to tell you not to be surprised when you get no information back. (Except, perhaps, a 'ding' sound.)