Skip to main content

Prerequisites for adding MSSQL Server

Microsoft SQL (MSSQL) Database Server Pre-Requisites :

This part will detail what user privileges or access rights you need to delegate, what roles you need to assign and what permissions and grant and the methods to grant such permissions in order to add MSSQL database to Motadata solution

User Privileges/Access Rights for a MSSQL :- Minimum User Privileges: User should be permitted to access MASTER database & MSDB database. User Roles & Permissions:- Roles: public + db_datareader should be selected for both MASTER and MSDB databases. For MS SQL 2005, 2008 and 2012, 2014 user roles: Database Accessed: Master Permit in Database Role: db_datareader & Requires VIEW SERVER STATE permission on the server. Methods to Grant Access:- To grant VIEW SERVER STATE, you can use any of the following methods :

  1. Execute the following query:

GRANT VIEW SERVER STATE TO username;

  1. In SQL management studio for user choose Properties -> Securables -> Click Add ( under securables ) -> choose “All objects of the Types…” -> choose Servers -> choose Grant for “View server state” permission.
  2. SQL Job Monitoring Requirements: To monitor SQL jobs the user should be a member of sysadmin or members of one of these fixed database roles roles:
    1. SQLAgentUserRole
    2. SQLAgentReaderRole
    3. SQLAgentOperatorRole