Post reply

Restricting DTS?

  • April 26, 2004 at 1:17 pm

    #101984

    Hello,

    MSSQL 7 w/SP2 and MSSQL2000 w/SP3

    I did some preliminary testing in EM2000 and have found that as long as a user has database access and READ rights on the tables within that database, they can use DTS to export data.

    Any startegies on controlling users accessing and using the DTS tool?

    Many thanks. Jeff

  • April 26, 2004 at 1:48 pm

    #504263

    Locally you can restrice access to MSDB which would help prevent them from creating a dts on the machine, however they could still create one on their own machine. Other than that, you could run a process that checks sysprocesses, the program_name in particular and kills any connections that show up as using DTS Designer and a non sysad login.


    Shamless self promotion - read my blog http://sirsql.net

  • April 27, 2004 at 6:02 am

    #504316

    That's a good work around, but again, it's not an integrated solution.

    In one of my previous tests, I discover that even if the user does not have the rights to create & save a DTS on the server, it will still be able to create the DTS in the design mode, and will be able to run it, but not able to save it....

  • April 27, 2004 at 1:06 pm

    #504386

    How about not granting users read access? IF they shouldnt be reading the data, it is the correct solution.  If they CAN read the data, what's the problem?

    If they should only view some of the data, you should be using a behind the scenes login name which they never are privy too and then have them login using a forms based (i.e. password lookup) approach and never actually be logged in as a user that has direct SQL access to the tables.

  • April 27, 2004 at 1:23 pm

    #504389

    Hi,

    to further elaborate my environment ... we allow developers DDL/DML rights (db_ddladmin; db_datareader; db_datawrite) in development and in the production environment, they have only db_datareader rights. I believe that the develper should be able to see their own database, even in production, to assist with customer service calls as they are the appplication coordinators/gatekeeper for business end users.

    Many thanks. Jeff

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply