Post reply

Access to Prod Database SQL Server to developers in order to Work with DTS

  • February 13, 2007 at 10:29 am

    #63043

    Our SQL developers has approached me indicating that they are unable to use DTS unless theeir domain account has access to the database SQL server. The argument that when using the product connections and everything based on the connection to the server where the DTS is being defined. 

    We have an application that requires loading data into a table(on our server) from an external database on another server in a nightly basis.   

    On our environemnt only domain administrators or DBA have access to the database SQL server. Our production SQL server version is 2000 SP4 and the OS version is 2003.

    Is there any way I can grant any type of role or rights to the developers login/domain account in order to use DTS effectively without granting access to the server per se or give them the SA password?

    I wonder how other companies work this issue out. 

  • February 13, 2007 at 10:39 am

    #689521

    I would recommend not granting them access to the production server.  If all of your developers are exporting data via DTS from your production server all day, what does that do to your performance?  If they truely need production data, I would recommend restoring your nightly backup on a test server somewhere where they can get to the data.  There is a side benefit to this as well in that you will be regularly testing the validity of your backups!

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • February 13, 2007 at 2:58 pm

    #689591

    Hi,

    It depends what DTS is doing. Some packages need to create or use files or other resources on the server. Let developers to try this package in the development environment, work with them to find out that server resources are accessed, so for example if a file is created then maybe this account just needs write access to a certain folder or a share. When all permissions will be determined in the development environment, move the package tot he production server granting the same rights.

    Regards,Yelena Varsha

  • February 13, 2007 at 3:17 pm

    #689595

    The packages were created on the production server and now need to be modified. The developer can't run it from its local computer because the ODBC connection that the DTS uses. Also the db server has the installation of a 3rd party vendor product that is not installed on any of the devlopers client. 

    We want to avoid granting logon access to developers to any db production server . It will be a nightmare. We are looking for a solution where the developer can run/modify the DTS package from their client machine. 

    I wonder how other companies handle this situation...

  • February 14, 2007 at 10:07 am

    #689758

    "I wonder how other companies handle this situation..."

    in our company, we have typically three environments: dev, staging and production. The staging and production are identical except maybe the amount of data.

    The developers can wreck havoc on the dev servers all they want. once they are happy with their DTS and what-nots, these are brought over to the staging server and tested out. After the QA team is satisfied with the mods, then and only then, the new DTS & etc are implemented on the production servers.

  • February 14, 2007 at 10:11 am

    #689760

    Thoams Vu,

    Based on your answer the developers wont have logon (Remote Desktop Access) access to the PRoduction Server, correct?

  • February 14, 2007 at 10:45 am

    #689772

    We use the procedure that Thomas described in our shop.  DTS packages are treated the same as stored procedures.  Developers and users test them in dev and test, then the DBAs move the packages and or procedures to the production servers.  Developers do not have access to the production servers.

    Greg

    Greg

  • February 14, 2007 at 11:09 am

    #689779

    Our problem is that the packages were done on the PRODUCTION environment. Our test environment does not have the same components in this case drivers for the ODBC connection to an external server(vendor) needed for this package to run.

     

  • February 14, 2007 at 11:03 pm

    #689874

    well this approach is really not good. The DTS packages were done directly on the production. You should have got the drivers and all other things necessary for this testing in the dev environment. What if the production DB goes down due to these testing.

     

    I would suggesst you to have the dev environment in sync to the productiona nd let the devlopers do all the testing there.

  • February 16, 2007 at 11:54 am

    #690295

    There is nothing you can do when you inherent the environment....

    We already notify developers that from this point on DTs will be scripted form the test/staging environment.

     

Viewing 10 posts - 1 through 9 (of 9 total)

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