Documentation forServer Configuration Monitor

Permissions required to monitor MS SQL Server Essentials in SCM

Most of the elements in the built-in profile for MS SQL Server Essentials can be monitored using the default public login (see Create login). Some SQL elements require additional permissions.

Element Permissions required

Recovery mode

Create login

Stored procedures

Create login
Server configuration Create login
Database files Create login
Database options

Create login

Default data and log location

Create login

Filegroups Create login
Linked servers Create login
Logins Create login
Policy management Create login
Database backup Create login
DB schema - tables Create login
Security Create login
Server properties Create login
Suspect pages Create login
TempDB Create login
Triggers Create login
Query optimizer hotfixes Create login
DB schema - columns Create login
DB schema - views Create login
DB schema - indexes Create login
System database objects Create user in [model] database
Database mail

Create user in [msdb] database

Grant execute permission to mail procedures

SQL agent jobs

Create user in [msdb] database

Grant 'select' permission to 'sysjobs' table

SSIS packages Create user in [msdb] database

Grant 'select' permission to 'sysssispackages' table
High availability Create user in [msdb] database

Grant 'select' permission to 'log_shipping_primary_databases' table

Grant 'view server state' permission
Server configuration Grant 'view server state' permission
Instant file initialization Grant 'view server state' permission
Registry entries Grant 'view server state' permission
Mirroring

Create user in [master] database

Grant 'select' permission to 'database_mirroring_witnesses' table

Permissions Add login to 'securityadmin' role
Remove login Remove login

MS SQL Server Essentials permissions

Permission Command

Create login

USE [master]
GO
CREATE LOGIN [ScmUser] WITH PASSWORD=N'Password1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create user in [model] database

USE [model]
GO
CREATE USER [ScmUser] FOR LOGIN [ScmUser]
GO
Create user in [msdb] database
USE [msdb]
GO
CREATE USER [ScmUser] FOR LOGIN [ScmUser]
GO
Create user in [master] database
USE [master]
GO
CREATE USER [ScmUser] FOR LOGIN [ScmUser]
GO
Grant execute permission to mail procedures
USE [msdb]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_configure_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_account_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_profile_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_profileaccount_sp] TO [ScmUser]
GO
GRANT EXECUTE ON [dbo].[sysmail_help_principalprofile_sp] TO [ScmUser]
GO
Grant 'select' permission to 'sysjobs' table
USE [msdb]
GO
GRANT SELECT ON [dbo].[sysjobs] TO [ScmUser]
GO
Grant 'select' permission to 'sysssispackages' table
USE [msdb]
GO
GRANT SELECT ON [dbo].[sysssispackages] TO [ScmUser]
GO
Grant 'select' permission to 'log_shipping_primary_databases' table
USE [msdb]
GO
GRANT SELECT ON [dbo].[log_shipping_primary_databases] TO [ScmUser]
GO
Grant 'select' permission to 'database_mirroring_witnesses' table
USE [master]
GO
GRANT SELECT ON [sys.][database_mirroring_witnesses] TO [ScmUser]
GO
Grant 'view server state' permission
USE [master]
GO
GRANT VIEW SERVER STATE TO [ScmUser]
GO
Add login to 'securityadmin' role
USE [master]
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [ScmUser]
GO
Remove login
USE [master]
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N'ScmUser')
DROP USER [ScmUser]
GO

USE [model]
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N'ScmUser')
DROP USER [ScmUser]
GO

USE [msdb]
GO
IF EXISTS(SELECT * FROM sys.database_principals WHERE name = N'ScmUser')
DROP USER [ScmUser]
GO

USE [master]
GO
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'ScmUser')
DROP LOGIN [ScmUser]
GO