?
Solved

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

Posted on 2007-11-20
6
Medium Priority
?
693 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 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 2000 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

589 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