SQL Server Minimal Permissions Guide (125598)

Generally the permission requirements of the database accounts can be categorized according to the user accounts that use the connection:

1. A Q1IM administrative user would require all of the permissions listed in the Getting Started manual.
2. A Q1IM “end user” (e.g. web frontend user) would only need to be a member of the role “basegroup” created during installation of Q1IM database.

Here is a detailed explanation of some critical permissions:

Server role “processadmin”:

We use this permission for executing the KILL command. KILL is used in:

• the stored procedure vid_ClearConnects
• switching to SingleUserMode during Migration

vid_ClearConnects will terminate connections that haven't been used in a while. It is executed by a SQL Agent Job of the same name. You can remove this permission and either disable the SQL Server Agent Job or change the owner of the SQL Server Agent Job to an account with the necessary permissions.

Server role "bulkadmin":

This permission is not required in the standard. However, in some customer situations stored procedures with bulk statements are being used. In this case, "bulkadmin" would be required in order to execute or compile the customer stored procedure.

Permissions “Execute” on Master:

This permission is necessary for starting the SQL Server Agent Service, in order to make sure that the maintenance jobs and most importantly the database scheduler job is executed regularly. This is done through the stored procedure "vid_SchedulerPkg_AgentStart". This procedure is not executed during normal operation, but could be used by Identity Manager adminstrators to start the SQL Server Agent if they don't have access to the SQL Server and no DBA is available. If this this maintenance procedure is not required the permission can be removed.

Database role “SQLAgentOperatorRole” on msdb:

We use this permission to create or change jobs for the SQL Server Agent. It is necessary for the following procedures:

• vid_SchedulerPkg_AgentState
• vid_SchedulerPkg_AlterInterval
• vid_SchedulerPkg_AlterJobAction
• vid_SchedulerPkg_CreateJob
• vid_SchedulerPkg_DisableJob
• vid_SchedulerPkg_DropJob
• vid_SchedulerPkg_EnableJob
• vid_SchedulerPkg_StopJob

This permission is not strictly necessary during operation. It will only be requred if you try to re-create the SQL Server Agent Jobs by executing "vid_CheckDefaultSchedules" and "vi_CheckDefaultSchedules".

Database role “SQLAgentUserRole” on msdb:

Our Frontends include an interface for inspecting the pending database operations (called "database dialog" hereafter) and starting the SQL Server Agent Job which executes the database scheduler. This feature requires the SQLAgentUserRole.

Without the SQLAgentUserRole an error will be thrown if you press the "Start agent" button in the database dialog.

The SQLAgentUserRole permission can be removed if this feature is not used.

Database role “db_Datareader” on msdb:

The database dialog uses the following stored procedures which require the "db_Datareader" role to access the necessary data:

• vid_SchedulerPkg_JobStartable
• vid_SchedulerPkg_JobExists
• vid_SchedulerPkg_JobNextRun
• vid_SchedulerPkg_JobLastRun
• vid_SchedulerPkg_JobName_prep
• vid_SchedulerPkg_StartJob

In some of these procedures we need the db_datareader permission to determine the "Last Run" and "Next Run" times and compare it against the current time.

This permission is required for the database scheduler to allow it to check if the procedure "vid_DBSchedulerStartDailyJobs" has already been executed after the defined maintenance hour. Without this permission the database scheduler will terminate with an error.

Since we only require read permissions and because job data is not usually sensitive we strongly suggest keeping this permission.

Important: When removing maintenance jobs make sure to put solutions into place for the tasks they would normally perform!