Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    93

    Unanswered: Permissions for Database Mirroring

    I am using SQL 2008 R2 Standard Edition. The Mirroring is working fine for the Database between 2 SQL Servers.

    What permissions can I give to the Application User so the he can see the Mirroring Details under properties when you right click the Database ? I am trying to avoid giving sysadmin.
    The permissions for Database Mirroring monitor was dbm_monitor role in msdb. Once I gave that, the user is able to access the Database Mirroring Monitor but he still can't see the Mirroring Details under properties of the Database.

    Any help will be appreciated.

  2. #2
    Join Date
    Oct 2003
    Posts
    93
    Can any SQL Guru who can help on this one ?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Try granting select permissions on sys.database_mirroring. Alternatively, you could grant VIEW SERVER STATE, but that is going to bring a lot more baggage along.

  4. #4
    Join Date
    Oct 2003
    Posts
    93
    1) Select permission on sys.database_mirroring in master doesnt help.
    2) Even VIEW SERVER STATE doesnt help.

    The only thing which has worked so far is giving dbo access to Master. Its better than sysadmin but will still try to lower the permissions further.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There are several db mirroring views in master. Do a search for things with mirror in the name, and you should get a list of things to try narrowing the permissions to.
    Code:
    select name
    from sys.all_objects
    where name like '%mirror%'

  6. #6
    Join Date
    Oct 2003
    Posts
    93
    Mcrowley,
    Thanks for the input. I gave blanket db_datareader on master. That didnt help. So it doesn't make sense to try for the individual sys tables in master.
    Last edited by mgupta; 07-11-11 at 12:13.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    One other thing you can do is run a trace for the permission errors. Those should give you not only the objects involved, but the required permissions.

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    As normal with SSMS, it touches a lot of objects. Our list for database mirroring properties is:
    Code:
    master.sys.databases
    sp_executesql
    sys.sp_db_vardecimal_storage_format
    msdb.dbo.syspolicy_configuration
    master.dbo.sysprocesses
    sys.master_files
    sys.database_recovery
    sys.database_mirroring
    sts.change_tracking_databases
    sys.allocation_units
    sys.partitions
    master.dbo.spt_values
    sys.endpoints
    sys.server_principals
    sys.services
    sys.database_principals
    sys.data_spaces
    sys.extended_properties
    Happy hunting, and be careful
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  9. #9
    Join Date
    Oct 2003
    Posts
    93
    thanks to Roac and Mcrowley for all the Help !!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •