SQL Server Permissions – sys.database_mirroring in SQL Server

permissionssql serversql-server-2012

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?

Best Answer

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

Related Solutions

Sql-server – Permission to create Spatial Index

Microsoft connect ticket was resolved with wording changed:

Please refer to link below (changes have not been made on BOL yet, 1 dec)

https://connect.microsoft.com/SQLServer/feedback/details/706766/books-online-suggested-permission-to-create-spatial-index-incorrect

Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

First you have to

Create Login with create database permission. Note that the login cannot drop any databases.

USE [master]
GO

CREATE LOGIN [Kin] WITH PASSWORD=N'somePassw0rd', DEFAULT_DATABASE=[master],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
-- Grant create any database, but not drop or alter any database
 GRANT CREATE ANY DATABASE TO [Kin]
GO

Map the login to the database that you want to have permission to alter or drop.

-- for drop database, you just need db_owner
USE [test_drop]
GO
CREATE USER [Kin] FOR LOGIN [Kin] 
 GO

Add the user to the db_owner role.

  EXEC sp_addrolemember 'db_owner', 'Kin'

Now test it for create / alter and drop :

EXECUTE AS LOGIN = 'Kin'

create database test_drop


EXECUTE AS LOGIN = 'Kin'
use test_drop

create table test (fname char(1))


EXECUTE AS LOGIN = 'Kin'
select * from test

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set offline

EXECUTE AS LOGIN = 'Kin'
alter database test_drop 
set online

EXECUTE AS LOGIN = 'Kin'
drop database [test_drop]

Try to drop some other db :

EXECUTE AS LOGIN = 'Kin'
alter database [test_kin] 
set online

Below is the error :

Msg 5011, Level 14, State 9, Line 2 User does not have permission to alter database 'test_kin', the database does not exist, or the database is not in a state that allows access checks. Msg 5069, Level 16, State 1, Line 2 ALTER DATABASE statement failed.

Best Answer

Related Solutions

Sql-server – Permission to create Spatial Index

Sql-server – How to enable a SQL Server 2012 login to create, alter and drop the created databases

Related Question