User Permissions

The question comes up occasionally about user permissions to use Database Health Monitor.

The following script will set up a user that will get you most of the access. There are some thing that require sysadmin permission like some of the DBCC commands that are used to pull information from the system. But without sysadmin permissions you can still view many parts of the program.

The following script will get you most of the way there.

USE [master]
GO

CREATE LOGIN [DbHealthUser] WITH PASSWORD=N'Super!Secret!Secure!Passwpord', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER LOGIN [DbHealthUser] enable
GO
use [master]
GO
GRANT VIEW SERVER STATE TO [DbHealthUser]
GO

CREATE USER [DbHealthUser] FOR LOGIN DbHealthUser;
go
GRANT EXECUTE ON xp_readerrorlog TO [DbHealthUser];

go
GRANT VIEW ANY DEFINITION TO [DbHealthUser]

use [master]
GO
GRANT VIEW ANY DATABASE TO [DbHealthUser]
GO

-- Do the following for each database
USE [YourDatabase]
GO
CREATE USER [DbHealthUser] FOR LOGIN [DbHealthUser]
GO
USE [YourDatabase]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DbHealthUser]
GO
USE [YourDatabase]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DbHealthUser]
GO