Solved

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

Posted on 2007-11-20
6
665 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 42

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 42

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

735 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