I have access to an enterprise level DB used throughout the organization with tight constraints on who can access things. It contains almost all our data. I am a user of that DB with only read rights. I also have admin rights to my 'team's server' where I can create DBs for projects, small data storage etc. I can create Server Agent jobs etc here in addition to new tables. Both are SQL(but not sure on edition, I am confident both are 2008 or later, with the local copy probably at least 2012)

What I need to do is use a T-SQL query I have built to pull data from the main server and run it every so often (week or two) and store it in smaller DB on a different server. This is so I can grant access rights to another user who can't access the main DB (the data is needed for a third party application). (It is all legit with the managers, there are just security issues with giving them rights to the full data.)

Can I do that, if so how? I thought a SQL Server Agent Job would work, but I don't know how to set that up. In the Steps tab I can insert a query, but the list only populates with the DBs I have admin rights to. I need to run the query on the org level DB and then have it save the results into a new DB I have just created. Does the job have to be setup in the higher level DB, or can it be setup in the lower one and just ping the other? I thought of maybe adding a line to the SQL telling it which DB to ping, but I don't know how to handle telling it to use Windows authentication for the server password.

Sorry, I don't know which question is the right one, so I put a few showing my line of thinking.

  • You will need to write a query that will copy the data or create a SQL Server Integration Services (SSIS) package to copy the data. You can then use a SQL Server Agent job to run the query or the SSIS package on a scheduled basis. The question is a bit broad as it seems you're asking for a ETL how-to guide, so you may want to narrow it down to a specific question. It doesn't matter where the job runs (unless you don't have remote connections enabled on the DEV server). As long as you have connectivity to the servers, you could run it from Mars. – Tony Hinkle May 28 '19 at 17:34
  • Sorry for the general-ness of the question. I am not a DB person, just a user with some basic SQL knowledge trying to finish off a project that ended up requiring up-to-date data but realizing they couldn't ping the main server themselves. I don't know anything about ETL. Which of your options would be easiest for a beginner? If a how-to-guide is needed, do you have a link to a good resource for beginners? All I have is access to two servers already setup, admin rights to one, and then a query I wrote to get the data from the main DB for use (currently in excel, but desired the new DB) – user2103050 May 28 '19 at 20:19
  • The simplest thing is probably to create a Linked Server on your team's server. See docs.microsoft.com/en-us/sql/relational-databases/… – David Browne - Microsoft May 28 '19 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.