Results 1 to 6 of 6

Thread: DTS and DBCC

  1. #1
    Join Date
    May 2002
    Posts
    55

    Unanswered: DTS and DBCC

    Question 1:

    When I setup DTS package, what kind of privilege I need to get.??
    Only SA can setup or there is other server privilage can be add.


    Question 2:

    How do I setup a database maintain job like ( backup, DBCC reindex, update statistics ) , using DTS or JOBs ? it there any suggest scripts?

    Thanks in advance.

    GY

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    1) In general DTS package contains one or more steps that are executed sequentially or in parallel when the package is run. When executed, the package connects to the correct data sources, copies data and database objects, transforms data, and notifies other users or processes of events. Packages can be edited, password protected, scheduled for execution, and retrieved by version. So normal user privileges are enough to accomplish the task and it depends purely on level of privilege for that user on the database.

    Then coming to DTS security, if you set an owner password, the package user needs the password to edit or run the package. If you set a user password, you also must set an owner password.


    2) You can setup Maintenance Jobs to perform backup, DBCC checks and optimization tasks, from Enterprise Manager goto Management and there you can find DB Maint.plan to setup.

    By all means refer to BOOKS ONLINE for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2002
    Posts
    55
    Thanks Satya.

    That means any use can create DTS package only if he or her has the privilege for the database objects ( tables, views , etc). SA is not the only id we can create DTS


    GY

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    1. Why not try it and see. Any user can create a package - remember dts is a client utility which can connect to sql server.
    It's usually easier to run as sa though.

    2. You can schedule a scheduled job with t-sql tasks and put any statement(s) in it you wish. Usually better to put individual operations as separate steps or call an SP to do them.
    I'm not a fan of maintenance plans - would rather code the steps myself.

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    True, whatever referred by Nigel (Thanks).
    It works as my developers and support staff can do it.
    With DTS you can do it...
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    May 2002
    Posts
    55
    Thanks nigelrivett and satya

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •