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.