SQL Server:User Permissions

Grant permissions to users to create jobs in Sql Server

There are roles in msdb database that help database administrators to have better granular control over job creation, execution, and browsing:
SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole.

To grant users permission to create and schedule their own jobs, use this script:

USE [msdb]
GO
CREATE USER [UserName] FOR LOGIN [LoginName]
GO
USE [msdb]
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'UserName'
GO

To grant a domain group permission to create and schedule their own jobs, use this script:

USE [msdb]
exec sp_addrolemember 'SQLAgentUserRole', 'DomainName\GroupName'

Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.

You can add user to the SQLAgentUserRole in Management Studio (Right-click the login you wish to add to a SQL Server Agent fixed database role, and select Properties):

-----------------------------

Configuring user permissions for Microsoft SQL Agent

Microsoft SQL Server defines SQL Server Agent roles that govern accessibility of the job framework. The SQL Server Agent jobs are stored in the SQL Server msdb database.

To install Websense Log Server successfully, the user account that owns the Websense database must have membership in one of the following roles in the msdb database:

*SQLAgentUserRole

*SQLAgentReader Role

*SQLAgentOperator Role

The SQL user account must also be a member of the dbcreator fixed server role.

Use Microsoft SQL Server Management Studio to grant the database user account the necessary permissions to successfully install Log Server.

1.On the SQL Server machine, go to Start > Programs > Microsoft SQL Server 2005 or 2008 > Microsoft SQL Server Management Studio.

2.Select the Object Explorer tree, and then go to select Security > Logins.

3.Select the login account to be used during the installation.

4.Right-click the login account and select Properties for this user.

5.Select User Mapping and do the following:

a.Select msdb in database mapping.

b.Grant membership to one of these roles:

*SQLAgentUserRole

*SQLAgentReader Role

*SQLAgentOperator Role

c.Click OK to save your changes.

6.Select Server Roles, and then select dbcreator. The dbcreator role is created.

7.Click OK to save your changes.