1 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Using XML Reports to write to a different db

I'm trying to use XML Reports to SELECT ... INTO ... a separate database I have created called NBNReporting. The database is on the same instance of MSSQL2005 as NBNData. When I try and run my report, it gives the following error:

'The server principal "DOMAIN\CharlesR" is not able to access the database "NBNReporting" under the current security context'

Is it possible to resolve this issue by changing the security settings? If so, what security settings do I need to change? The query runs fine in SQL Server Management Studio, so it's not my own security settings that are causing the problem but something within the Recorder layer.

Many thanks,

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Using XML Reports to write to a different db

Having you tried giving the NBNUser login access to the other database? This is what Recorder will be using (unless you are using Trusted Connections).

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Using XML Reports to write to a different db

Actually, looking at the error you obviously are using Trusted Connections. Recorder uses an Application Role, which effectively replaces your normal security with a level of security appropriate to your login. This may be why your account cannot access this database from within Recorder.

John van Breda
Biodiverse IT

johnvanbreda's Website

4 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Using XML Reports to write to a different db

Thanks John. I've now managed to resolve the issue by issuing the relevant permissions to the guest user account in the reporting database and the XML report now works.

To give a little background to others reading this, when I researched how to access another database when using an Application Role I discovered that this can be done via the guest account. Simply grant the Guest account in the secondary database the permissions it needs to get the job done and XML Reports will let the query run.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website