Find "Mystery processes" on a MS SQL Sever environment? (SSIS, databases, etc) I inherited a collection of Instances/ MS SQL Databases/ SSIS scripts on a server. I am now the interim "accidental DBA."

I thought I was familiar with everything there, but apparently not. It's all completely undocumented.

I myself am not even currently the 'master admin' of the -- well ... I'm not even sure how that works. Can you group all instances on a server together to have the same master admin account? It's possible one of IT guys who knows nothing of the contents of these dbs has root access of some sort.

Let's just say -- I'm familiar with probably 80% of all processes that go on in this server. It's a collection of databases and SSIS scripts.

HOWEVER, I have noticed that some tables --- well, WHATEVER is writing to them, I have no idea. I don't have visibility to the (presumably SSIS since everything else is SSIS) scripts that are writing to them.

The few SSIS scripts I do have visibility to --- well there's a different "collection" for every Instance on this server.

Well - anyway -- there may be an IT contact that has root access/ admin access to something. The old DBA is gone.


Assuming this IT contact has top-level access to every Instance/ Database on this server, should I just access him for a high-level mapping of every instance-database and potentially access? (I'm literally the only one working with these databases). A lot of stuff I do not have access to, I'm either unaware of its existence, or its hidden.
What happens if this IT contact does not have admin access to some "pieces" or databases here? Are we screwed?
Should I request remote access to the actual Server (probably Windows Server 2012) that this database is hosted on? I'm wondering if that would help me investigate/ find where these Mystery SSIS scripts are.
How do I find out what robot-script is writing to a bunch of tables? Should I set up SQL Server Profiler? I guess it's called Extended Events now? What access/ permissions are required for this (I assume I just need trace permissions on the database where the tables are being updated)? Again, I'm well aware of the database where the updated tables are located --- there are SSIS scripts in that Instance, but none that write to these tables. Some "Mystery Process/ Script" is doing so daily.
Assuming this IT contact has top-level access to every Instance/
  Database on this server, should I just access him for a high-level
  mapping of every instance-database and potentially access? (I'm
  literally the only one working with these databases). A lot of stuff
  I do not have access to, I'm either unaware of its existence, or its
  hidden.
  


If you're now the defacto DBA you should, in accordance with your IT/Security policies, arrange for administrative access to each instance. This will likely be sysadmin access to each instance. There is no collective 'master admin' across all instances on a server. Instances are isolated from a security perspective and admins on one do not necessarily get/need access to another instance.


  
  What happens if this IT contact does not have admin access to some "pieces" or databases here? Are we screwed?
  


It is possible for local administrators of the actual server to regain access to a server when all sysadmins are lost. See this link for more information. Your IT contact will need local admin access to the server to use this fix.


  
  Should I request remote access to the actual Server (probably Windows Server 2012) that this database is hosted on? I'm wondering
  if that would help me investigate/ find where these Mystery SSIS
  scripts are.
  


Potentially yes. If there are SSIS packages stored on the file system then you may need remote access to view and manage those packages.


  
  How do I find out what robot-script is writing to a bunch of tables? Should I set up SQL Server Profiler? I guess it's called
  Extended Events now? What access/ permissions are required for this
  (I assume I just need trace permissions on the database where the
  tables are being updated)? Again, I'm well aware of the database
  where the updated tables are located --- there are SSIS scripts in
  that Instance, but none that write to these tables. Some "Mystery
  Process/ Script" is doing so daily.
  


Profiler and Extended Events are different tools, but both can be used for this purpose.

Check out this article series on SQLServerCentral.com for information on Extended Events. This will help you put together a session to capture the information you're after.

One additional note, don't be like the DBA who just left your company. As you start to piece together the packages, scripts, jobs and other bits of information about this server, document it. It will not only help anyone who comes in to provide SQL Server support, but you'll find it an invaluable tool yourself.