November 1, 2008 at 1:01 am
Hi All,
I have installed sql server 2005. To configure Biztalk apllication, the biztalk admin needs a login ;btsadmin' in sql server with sysadmin role for the installation n configuration only.
Once the installation is done I need to revoke the sysadmin role and assign securty admin role for btsadmin.So I went and revoked the sysadmin role and assigned the security admin role to 'btsadmin'.
By default after the biztalk configuration there are some jobs created with owner btsadmin.
jobs with owner btsadmin: these are running every minute
1.MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb
2.MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb
3.MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
So after I revoke the sysadmin role to 'btsadmin' all these jobs are failing continuously.
Can I change the owner to different login?
like NT AUTHORITY\SYSTEM (OR) SA?
What would be the solution for this?
Thanks
Madhu
November 1, 2008 at 9:53 am
Does bstadmin have access to the database on which all these jobs run. You dont have to give sysadmin priviledges, nor you have to assign any sysadmin logins to these jobs. But check if btsadmin has access to the main database.
Depending upon the action jobs are performing you might want to give btsadmin user those permissions on main database
Hope this helps.
Imran Mohammed.
November 1, 2008 at 10:09 am
If its just job ownership, you can set it to "Sa" and you should be fine.
November 1, 2008 at 6:09 pm
Thanks..
Generally Who are the members of the NT AUTHORITY\SYSTEM and BUILTIN\Administrators
what actualy these logins do? In what circumstances we use these logins?
In my case....there is only one backup job(created by configuring Biztalk) which needs sysadmin role for btsadmin login to run the job . All other jobs do not require sysadmin role, securityadmin is fine.
How can I acheive this?
Thanks
Madhu
November 1, 2008 at 9:44 pm
BuiltIn/Administrators are the local administrators of the Windows OS. these are added to SQL Server so that those who administer the server can administer SQL.
The System is for service accounts.
November 2, 2008 at 2:36 am
Yes,the bts admin has the Security admin role and ddladmin and dbaccess database roles on the particular databases that the jobs are using.But for one particular backup job,it needs sysadmin role as the biztalk security document says.So how can we map the btsadmin sothat it can have only sysadmin to run that particular backup job and the rest needs to have just security admin..
November 3, 2008 at 3:21 pm
Which backup job in particular? I only see one true backup job for BizTalk in our installation and it runs with the owner "sa".
November 3, 2008 at 3:48 pm
I did not mentioned here...
Here is the backup job 'Backup BizTalk Server (BizTalkMgmtDb)' which requires sysadmin role as per the biztalk security document. can we restict the user not having sysadmin to run this particular job?
November 3, 2008 at 7:03 pm
If Job only takes backup then assign the user to db_backupoperator database role for that particular database which it is taking backup of.
To take backup of the database user/login don't have to be a sysadmin.
Hope it helps,
IM
November 10, 2008 at 5:03 am
If the biztalk jobs are failing, you may need to set up a SQL Agent proxy for the biztalk account. See BOL for details of how to do this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply