The question today:  Should BizTalk Administrators be granted/given the “SysAdmin” role (authority) in the SQL database that supports BizTalk.

Generally, my answer is yes (at least on the BizTalk machine that is running SQL to support BizTalk, and here’s why.

1) The BizTalk Admins typically have to deal with the SQL Agent Jobs.  BizTalk installs about 14 jobs, and you’ll need SysAdmin at a minimum while doing the install.

2) BizTalk Admins sometimes need full power such as creating new databases, backing-up and restoring databases, attaching and detaching databases.

NOTE: Most companies should not be running the BizTalk database an their production databases on the same machine.  Most everyone separates the two for performance reasons.  Thus, giving the BizTalk Administrator the SysAdmin rights is only required on the BizTalk machine (or the SQL machine that supports BizTalk).  In larger BizTalk installation, even BizTalk and the BizTalk that supports SQL are separated onto two physical (or logical/virtual) machine names.

In some bigger companies, duties might be separated, such that there is really a person in the BizTalk Admin role, and that person handles the installs and the management of the SQL Agent Jobs.  In most companies, the BizTalk Developers play somewhat of a BizTalk Admin role as well (at least in the development, if not the production environment).

Further, in some companies, the developers are allowed to create SQL Agent Jobs related to the development database, while in other companies only the DBA will have that authority.

The corrollary of the above question would be this:  Why can’t I see the BizTalk SQL Agent Jobs when I open SSMS (SQL Studio Management Studio).  The answer is that you need the SysAdmin SQL role to see the Agent Jobs.

NOTE: If you want to be more granular, instead of granting SysAdmin, there are three roles specific to SQL Agent: SQLAgentUserRole (users get access only to jobs owned by them), SQLAgentReaderRole  (can view but not change all jobs, but also get update on their own jobs as per the prior role),  SQLAgentOperatorRole (everything in prior role, but can execute enable/disable any job in system, just still can only modify their own jobs).

<a target=”_blank” href=”https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/”>https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly</a>

Adding the SysAdmin role is demonstrate in both the GUI mode and the command mode here:

<a target=”_blank” href=”http://stackoverflow.com/questions/14814798/how-to-add-sysadmin-login-to-sql-server”>http://stackoverflow.com/questions/14814798/how-to-add-sysadmin-login-to-sql-server</a>