May 26, 2017 at 3:20 pm
HI,
Does anyone know if there is a way to stop users from running dtsx packages from connecting to SQL 2012? I keep all my packages in msdb that are properly tested. But I have users that will write their own dtsx packages that connect to my SQL 2012 box and will extract data to manipulate (usually incorrectly) and then blame me when their data is incorrect. The users have connect and read rights to the DB and I can't stop that, but what I'd like to do is stop them from connecting with VS. Anyone know if this is possible?
Thanks!
May 26, 2017 at 8:29 pm
It sounds like you have a bigger issue than SSIS. If the users have query permissions to the database (even if you could shutout SSIS), what's to stop them from querying through SSMS or Excel? They still will make incorrect inferences or incorrect conclusions if they don't understand the data.
Are they querying against a transactional system? or a data mart/warehouse? If they are querying a transactional system, you could create a data mart to give them the ad hoc access they need, but give you some control over what they see. If they are using the data warehouse, could some additional training help them understand the data?
Good luck,
Rob
May 27, 2017 at 12:34 pm
HI Rob,
Its a warehouse datamart setup. And yes they do the excel thing to me as well. Its health care and there are 25000+ employees. A lot of eager beaver getting the data and do calculations that are wrong instead of just asking us. Most of the time they're just trying to skew it in their favor so they get more funding or just look better. Anyways knew it was a long shot and I couldn't think of a way to block 3rd party apps from connecting when the user have read rights.
Thanks for replying!
Jeff
May 27, 2017 at 7:14 pm
it sounds like they are allowed to query the data with their tools of choice, but often write bad queries; maybe you could use the resource governor in that case; create a resource group, set limits on how much memory they can consume, and put their AD groups int hat group;
if they exceed the resource, they get disconnected.
Lowell
May 28, 2017 at 7:54 am
You said people are allowed to query data and that's not going to change. If that's the case, I don't know that the tool they use matters. They're going to find ways to write queries that are both incorrect and inefficient. Like Rob said, I think the issue here is training. If you teach them how to write queries correctly and efficiently, they get the correct data. I do find it curious how they can query data incorrectly and blame you.
May 30, 2017 at 11:08 am
There is possibly a job you could create that could run every couple of minutes. Add a table to a database they can't read that includes all their computer names. Then dump SP_WHO2 contents into a table variable. Run a cursor or WHILE loop to kill every SPID from those client computers using SSIS or Excel.
It's a hack job, but it'll do the job of stopping anything it catches in progress. Also, it'll create an absurd amount of complaints that they can't seem to get their spreadsheets & dtsx packages to work, which means more work for you fielding those complaints or choosing to ignore them.
I guess the big question is, why do they have read rights to your database if you don't want them crunching numbers (however incorrectly) in them?
May 30, 2017 at 1:06 pm
Hi,
Thanks for the replies. Brandie its not so much querying the data that is the issue. I typically just give them access to views that have all the proper joins done for them . Its the connecting with SSIS and extracting our data and then joining it to other data they might have and then presenting that as facts I don't like. But again there are more tools than SSIS and blocking them all would be pretty much impossible. Thanks for everyone's input. I'll just have to keep trying train them on the data.
Regards,
Jeff
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply