问题描述
当我尝试执行XA事务时，日志中出现以下异常:
I'm getting the following exception in my log when I try to perform an XA transaction:
我遵循了这些教程了解XA交易和如何使MSSQL Server XA数据源正常工作?在遵循了第一个教程之后，我还在SSMS中运行了以下命令:
I followed these tutorials Understanding XA Transactions and How to make MSSQL Server XA Datasource Work?After following the first tutorial I also ran the following command in SSMS:
我还要补充一点
验证用户是否有权访问主数据库，并且收到错误消息该用户已存在于当前数据库中".最后，我通过SSMS验证了角色SqlJDBCXAUser确实具有针对xp_sqljdbc_xa_init_ex授予的EXECUTE.
我正在使用的数据库显然不是master而是myDBName.就此问题而言，两者之间的唯一关联是MyUserName是myDBName的所有者，并作为用户在master中存在.
我的服务器在Windows XP SP3上运行(因此，第一个教程中提到的修补程序并不适用，因为它适用于XP SP2及以下版本，我知道我尝试运行此修补程序).
to verify that the user has access to the master db and I got an error that "the user already exists in the current database".Lastly I verified, via SSMS, that the role SqlJDBCXAUser does have EXECUTE granted in regard to xp_sqljdbc_xa_init_ex.
The DB I'm using is obviously not master but myDBName.The only correlation between the two, with regard to this issue, is that MyUserName is the owner of myDBName and exists as a user in master.
My Server is running on Windows XP SP3 (so the hotfix mentioned in the first tutorial is not relevant as it is meant for XP SP2 and under, I know as I tried to run the hotfix).
有人遇到过这个问题吗?我真的很感谢一些潜在客户.
谢谢，
伊泰
Has someone encountered this issue? I'd really appreciate some leads.
Thanks,
Ittai
更新:
我再次看了Microsoft中的第一个教程，有两段我不确定它们的含义，它们可能包含解决方案:
Update:
I've looked at the first tutorial, from Microsoft, again and there are two paragraphs which I'm not sure what they mean and they might contain the solution:
当他们说SQL Server instance时，是否表示包含几个数据库(包括master和myDBName)的sql server(我习惯于对oracle的术语有所不同)?我按给定的名称运行了xa_install.sql脚本，并且显示了use master.
When they say SQL Server instance, do they mean the sql server which contains several databases, including master and myDBName(I'm used to oracle terms which are a bit different)? I ran the xa_install.sql script once as it was given and it states use master.
这是第二段:
USE master
GO
EXEC sp_grantdbaccess 'shelby', 'shelby'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'shelby'
我不确定，但是我认为上面的粗体句子表示SqlJDBCXAUser角色应仅在master上定义，并且应授予其他访问myDBName的用户访问master的权限，然后添加到该角色中，并且会在使用myDBName数据库使用xa软件包时某种方式(不知道如何)启用它们.
I'm not sure but I think that the above bolded sentence says that the SqlJDBCXAUser role should only be defined on master and that other users which access myDBName should be granted access to master and then added to the role and that will somehow(don't know how) will enable them when using the myDBName database to use the xa packages.
更新2:这是来自SSMS的SqlJDBCXAUser角色下存储过程的安全设置的屏幕截图.
Update 2:This is a screenshot from SSMS of the stored procedure's security settings under the SqlJDBCXAUser role
推荐答案
我们只需要执行以下操作:
We only had to do the following:
USE [master]
GO
CREATE USER [UserName] FOR LOGIN [UserName] WITH DEFAULT_SCHEMA=[dbo]
use [master]
GO
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_commit] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_end] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_forget] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_forget_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_init] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_init_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_prepare] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_prepare_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_recover] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_rollback] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_rollback_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_start] TO [UserName]
GO
这篇关于MS-SQL Server，JDBC和XA事务的例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！