1

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?

  • Which SQL Server release or releases are you using? – RLF Sep 18 '15 at 13:23
  • I use 2012, developer 64-bit. – Erik Dahlen Sep 18 '15 at 13:28
2

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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