Solved

MS SQL Server: Trigger that needs to log/insert into different DB with different UserName/Password

Posted on 2007-11-20
6
668 Views
Last Modified: 2012-06-22
I need to add a trigger in my Database to log the Payments that we make into Accounting Database where I have SQL Server username and password.

The Accounting won't add me as a user in there DB but had given me there limited priviledge Username and Password. At insertion of a payment made from our System, I need to add the following trigger to insert into Accounting database's AccountLog table using the UserName and Password provided

INSERT INTO AccountLog( CustomerID, Emp_ID, AmountPaid,Date_Time)
select  inserted.CustomerID, inserted.Emp_ID, inserted.AmountPaid, inserted.Date_Time
from inserted  

Is it possible to do?
0
Comment
Question by:asp123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 20321031
..if the Username priviledges allow INSERT INTO AccountLog table (write permissions)
please clariy: what  rights your sql login\user has and where the trigger located?
0
 
LVL 3

Accepted Solution

by:
bcaff86 earned 500 total points
ID: 20321047
You should be able to set up a "Linked Server" to that Server.  You'll create a linked server called "AccountingSQL" and enter the credentials they gave you.  Now all queries using that server will run under those credentials.  (http://www.databasejournal.com/article.php/3116011)

You can then make a call like:

 Insert Into AccountingSQL.DBName.dbo.AccountLog( CustomerID, Emp_ID, AmountPaid,Date_Time)
select  inserted.CustomerID, inserted.Emp_ID, inserted.AmountPaid, inserted.Date_Time
from inserted  
0
 

Author Comment

by:asp123
ID: 20321073
The trigger is located in my(Application) Database where I am dbo and we use different Sql login/user for my Application, This sql login/user do not have any priviledge in the Accounting DB where the logging/insert need to take place. The Accouting people had given us a limited priviledge SQL login/user which can insert into Accounting table.

So I need to use the Accounting DB's SQL login/user to insert into Accounting Table from my Application DB as a Trigger in the Application DB.

Please let me know if you need more info.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 43

Expert Comment

by:Eugene Z
ID: 20321166
try bcaff86s 'Linked Server'
0
 

Author Comment

by:asp123
ID: 20321167
Just to clarify, Accounting DB is on the same SQL Server where my Application is.
0
 
LVL 3

Expert Comment

by:bcaff86
ID: 20321589
Well even if your db and the accounting db are on the same server you can use a LinkedServer...just create a linked server to itself.  Create the link using the OLEDB provider for SQL.  By doing this you'll create an alias for your SQL server that can be referenced as I described above.  Using that reference you'll ensure your using the right security context.  

Admittedly, it's a bit of a hack but it should work provided the limitations you've got.  
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question