Re: XA installation instructions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Wayne,

Yes this is a bit confusing and we should clarify this. SQL user defined
roles are a bit of a problem to work with, because they are defined per
database.

So if you decide to create your own role for security purposes, as we did
with the [SQLJDBCXAUser] role, you have to define the role in each database,
and add users in a per database fashion. In our case this [SQLJDBCXAUser]
role is strictly defined in master because it is used to grant access to the
SQL JDBC extended stored procedures which reside in master.

So you do have to first grant the individual user access to master, then
grant them access to the [SqlJDBCXAUser] role while logged into master.

So use this command for example for this 'wayne' user (SQL standard login
user named 'wayne'):
USE master
GO
EXEC sp_grantdbaccess 'wayne', 'wayne'
GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'wayne'Thanks for the feedback on the
installation instructions, we will get them updated to make all of this more
clear.

-Acey


"WayneHearn" <WayneHearn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0D52A17B-E02B-416C-8EAA-DA4C86A2AFC4@xxxxxxxxxxxxxxxx
I have followed the installation instructions provided with the released
SQL
2005 JDBC driver but adding a user to the SqlJDBCXAUser role doesn't work
as
advertised with the released version of SQL 2005 or I don't understand how
it
should work. The install script defines the role on the master database
but
when I execute sp_addrolemember [SqlJDBCXAUser], 'wayne' I get an error
stating:

User or role 'wayne' does not exist in this database.

Does that mean I have to add each user that might be used in an XA
transaction to the master database? Can someone explain how and why this
works this way? The docs delivered with the JDBC driver are a bit thin.

Thanks.


.

Relevant Pages

  • Fixed
    ... I also found this article that gives the proper way to move system dbs in sql 2008: ... The model exists where the master states it exists. ... This is the error log prior to detaching the model database. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server down
    ... Looks like you had a corruption in the master database. ... your SQL instance will not start no matter how hard the cluster tries to ... You may want to try to bring up the SQL instance without the ...
    (microsoft.public.sqlserver.clustering)
  • Re: Connecting to a remote sbs from a website?
    ... Your solution only works if your web app doesn't need to write data to the database. ... SQL server has always supported SQL authentication which does NOT require an SBS username/password. ... And if you aren't, somebody will find a way to break you, regardless of how well you think you've secured your master database. ...
    (microsoft.public.windows.server.sbs)
  • Re: The "Best Practice" for securing my vb.net/SQL connection.
    ... calls upon the stored procedures to access the tables in SQL 2005. ... The database will house mostly encrypted ... Create a master SP that has the rights to execute the existing app SPs. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Unable to connect to server
    ... that explains it - the definition for each database on a SQL Server is ... maintained in the Master database. ...
    (microsoft.public.sqlserver.security)