0

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.

  1. 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.

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

  3. 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.

  4. 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.

closed as too broad by Josh Darnell, hot2use, Marco, McNets, Max Vernon Apr 5 at 15:09

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • I can easily figure out how it works. The current scripts are mostly 'Ca Ca' anyway. However it will be a slow refactor. Piece by piece, fix it so the calculations are correct. Because the current scripts are the HMS Titanic. Not touching it is out the window. I need to locate them. A pearl of knowledge about some hackey calculation work-around can be useful and some stuff just needs to "keep working" short-term until they're completely rewritten. If I can LOCATE the SSIS scripts, I can apply short-term fixes to their horrific math. – user45867 Mar 25 at 21:17
3
  1. 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.

  1. 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.

  1. 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.

  1. 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.

  • Not only will I document it extensively and actually use service accounts for things instead of personal logins, but the math and SQL conventions will also be correct. But you are a lifesaver here with some of this info. Thanks. – user45867 Mar 26 at 0:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.