January 29, 2019 at 10:55 am
I had a list of users who have access to the folder where all our SSIS packages are and someone made some changes which caused the job to fail. Is there a way to find out who made changes? I am new to SSIS so I am not sure where to look for.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 30, 2019 at 4:47 am
How is the SSIS package run? If it's run from within SQL Server, meaning it gets deployed to the instance (and this is not the way that most people I know run them, they run them directly from the SSIS file), then you might be able to tell who made a change by using the default trace to track down who deployed it. Here's an example of tracking DDL info. I think it'll capture the SSIS stuff too.
Otherwise, this is why you should use some sort of code management like source control for SSIS. You'll know who made what changes when, how, etc.. There's no other way to track it internally that I'm aware of.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 30, 2019 at 6:04 am
Grant Fritchey - Wednesday, January 30, 2019 4:47 AMHow is the SSIS package run? If it's run from within SQL Server, meaning it gets deployed to the instance (and this is not the way that most people I know run them, they run them directly from the SSIS file), then you might be able to tell who made a change by using the default trace to track down who deployed it. Here's an example of tracking DDL info. I think it'll capture the SSIS stuff too.Otherwise, this is why you should use some sort of code management like source control for SSIS. You'll know who made what changes when, how, etc.. There's no other way to track it internally that I'm aware of.
The DDL triggers are a great idea (although I don't use SSIS and so don't know if they'll capture the SSIS stuff either). I'm curious though. You made it sound like having something under source control will prevent someone from making untracked changes. Is that possible? I thought that could only really be done with auditing.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 30, 2019 at 6:34 am
Jeff Moden - Wednesday, January 30, 2019 6:04 AMThe DDL triggers are a great idea (although I don't use SSIS and so don't know if they'll capture the SSIS stuff either). I'm curious though. You made it sound like having something under source control will prevent someone from making untracked changes. Is that possible? I thought that could only really be done with auditing.
Oh no. If people have access and can do whatever they want... they have access and can do whatever they want. No, I'm suggesting that you have to set up process and procedures (and better still, automation around both) such that people, even if they have the access, don't do whatever they want. It's how I worked for a very long time at my last job. It's what Redgate sells frankly, support for process and automation. However, that still doesn't stop a bad actor or rogue admin with more privilege than sense. But, if the company standards are such, you can come down on them like a wall. That part is fun.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply