Solved

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

Posted on 2007-11-20
6
654 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 42

Expert Comment

by:EugeneZ
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 42

Expert Comment

by:EugeneZ
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Webservices in T-SQL 3 31
sql select record as one long string 21 24
Microsoft Access Write errors seem to be caused by bit fields 4 36
sql server service accounts 4 27
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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