SQL Server 2005 Service Account & Permission Requirements

Posted by Harry Zheng on 2006-10-23 Leave a comment (0) Go to comments

Starting from SQL Server 2005, the SQL Server installation process automatically creates a set of local Windows groups to manager various service accounts used by SQL Server 2005.

It is recommended to always configure the service account using the “SQL Server Configuration Manager” utility, which puts the services account into the right group hence assign the correct permissions required to run the service.

SQL Server Database Service (SSDS)

  • Member of the local group SQLServer2005MSSQLUser$Hostname$InstanceName

SQL Server Analysis Service (SSAS) 

  •  Member of the local group SQLServer2005MSOLAPUser$Hostname$InstanceName

SQL Server Agent (SSAgent)

  • Member of the local group SQLServer2005SQLAgentUser$Hostname$InstanceName
  • Required Windows permissions:

Adjust memory quotas for a process
Act as part of the operating system (Windows 2000 only)
Bypass traverse checking
Log on as a batch job
Log on as a service
Replace a process level token

Required SQL Server Role: sysadmin

SQL Server Reporting Service (SSRS)

  • Member of the local group SQLServer2005ReportServerUser$Hostname$InstanceName
  • The accounts used to run the Report Server Web service and Windows service must have a SQL Server login and must have the Public and RSExecRole roles for the report server databases. The RSExecRole is created in master and msdb when you create the report server database.  All of the required logins, roles, and permissions are created and configured automatically when you use the Reporting Server Configuration tool to create or set the database connection.
  • Summary of rights granted to the SSRS account through RSExecRole on master and msdb databases:

USE master
GO

if not exists (select * from sysusers where issqlrole = 1 and name = ‘RSExecRole’)
BEGIN
 EXEC sp_addrole ‘RSExecRole’
END
GO

USE msdb
GO

if not exists (select * from sysusers where issqlrole = 1 and name = ‘RSExecRole’)
BEGIN
 EXEC sp_addrole ‘RSExecRole’
END
GO

————- Master and MSDB rights
USE master
GO
GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole
GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole
GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole
GO

USE msdb
GO

– Permissions for SQL Agent SP’s
GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole
GO
GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole
GO
GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole
GO

– Yukon Requires that the user is in the SQLAgentOperatorRole
if exists (select * from sysusers where issqlrole = 1 and name = N’SQLAgentOperatorRole’)
BEGIN
 EXEC msdb.dbo.sp_addrolemember N’SQLAgentOperatorRole’, N’RSExecRole’
END

 

 

Microsoft SQL Server
Leave a comment

0 Comments.

Leave a Reply

You must be logged in to post a comment.