How to create a user/login for sqlserver probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Introduction: 

How to manually add a user in SQL Server to be used in sqlserver probe:

When the passwords for local system administrator or sa user are not available during the time a sqlserver probe is deployed to monitor the MS SQL Server databases, a new user is needed to monitor the server for all default checkpoints and to produce the same outcome as the sa user. During the time, this user shall have limited access to application data.

This article provides instructions for how to create a new user and these instructions are used with:

Sqlserver probe 4.90-4.93

MS SQL Server 2008 R2 Enterprise

Environment

Release:

Component:

Resolution

Instructions: 

Run the following statements in the Management Studio:

USE [master]

GO

CREATE LOGIN [ca2] WITH PASSWORD=N'Passw00d',

 DEFAULT_DATABASE=[master], 

 CHECK_EXPIRATION=OFF, 

 CHECK_POLICY=ON

GO

use [master]

GRANT VIEW SERVER STATE TO ca2;

GRANT VIEW ANY DEFINITION TO ca2;

GO

use [master]

exec sp_adduser 'ca2';

exec sp_addrolemember 'db_owner', 'ca2'; 

 

use [msdb] 

exec sp_adduser 'ca2';

exec sp_addrolemember 'db_owner', 'ca2';

 

use [model]

exec sp_adduser 'ca2';

exec sp_addrolemember 'db_owner', 'ca2';

 

use [tempdb]

exec sp_adduser 'ca2';

exec sp_addrolemember 'db_owner', 'ca2';

GO

Add the same user for all non-system databases that need monitoring and by default such user will be granted a public role.

use yourdb

exec sp_adduser 'ca2';

then you can use this user ca2 and its password in the sqlserver probe configuration.

 

Additional Information

Additional Information:

For these two checkpoints to run without an error you can assign user ca2 the ‘sysadmin’ server role:

logfile_usage_with_avail_disk

fg_freeSpace_with_avail_disk