?
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
?
694 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

569 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