Many SQL servers, especially data warehouse servers, have tons of DTS packages. Therefore, managing DTS packages is a task that a lot of DBAs have to do. In the second of a series articles, I'll talk about some of the DTS management issues I faced and how I resolved them. The issues I will address in this article are: DTS

package ownership and editing, scheduling DTS packages, viewing package logs, and stopping a running package in DTS designer. Again, I hope this information will be helpful to you.

For my article on SQL Trace and database access auditing, click here.

Background

There are 6 developers on our OLAP/Data Warehouse team. Before I started working on our data warehouse server, everybody is

SYSADMIN on the servers, which is truly, truly bad. They've been creating databases, granting access, and basically

doing any kind of activities on SQL server. As a result, we have tons of DTS packages. Many of them are just for testing or just been used once and never got

cleaned up. All the developers access the server using Windows Authentication.

To centralize the control of this database, I decided to remove SYSADMIN role from all the developers and only grant them

db_owner role on the databases that they actually work on. This has eliminated many confusions and miscommunications we had and increased the stability of our servers.

However, when all the developers belong to the SYSADMIN role, there are no problems for them to edit each other's packages,

scheduling packages, viewing the package logs, etc. I'll give you a brief explanation

of all those issues and provide solutions I used to resolve those issues

DTS Package Ownership and Editing

In SQL Server, all DTS packages are managed and stored in the msdb database. Although developers could still open and review each DTS packages and use the Save As function,

only the owner or a member of the SYSADMIN role could edit it. In my case, because the developers was assigned Windows Authentication, each packages created has the developer's Windows login ID as its

owner. Therefore, although the 6 developers work on the same project, they couldn't edit each other's

package, because they all have different owners . Yet another reason why SQL authentication is better than Windows Authentication

(See

Andy Warren's article on SQL authentication).

Looking under the hood, I found out that the DTS designer calls sp_add_dtspackage stored procedure in the

msdb database when saving a local package. If you open this procedure and look at the code toward the end of the procedure, you will see the following code

--// Only the owner of DTS Package ''%s'' or a member

of the sysadmin role may create new versions of it.

IF (@owner_sid <> SUSER_SID() AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'),

0) <> 1))

BEGIN

RAISERROR (14586, -1, -1, @name)

RETURN(1) -- Failure

END

Similarly, when deleting a package, a similar stored procedure, sp_drop_dtspackage, is called and it contains the same code that prevent non-owner or

non-SYSADMIN from deleting this packages.

There are 2 ways to resolve this. One unorthodox approach is to remove the checks from the stored procedure. This is not recommended because both of the stored procedures are

considered system stored procedures. When you modify them, you might nullify all the support agreement with Microsoft, if there are any ;-).

Another approach requires a little more work but is worth the effort. First, you can create a SQL login that has the same rights and authority as the Windows login. Then, using the undocumented stored procedure,

sp_reassign_dtspackageowner, you can change the owner of the DTS package from the Windows login ID to this newly created SQL login ID. The following is the syntax for this procedure:

sp_reassign_dtspackageowner [@name =] 'name',

[@id =] 'id',

[@newloginname =] 'newloginname'

After this is done, you need to ask your developers to change their connection setting from using Windows Authentication to using SQL authentication. Afterwards, you can choose to delete the Windows authentication account in SQL server.

Since all developers now use the same SQL login and this login is the owner of

the DTS package, they should be able to review each other's packages.

What happens to the connections within a DTS package that uses the developer's Windows authentication, when the package was first created? Nothing. The package will work as usual, even that Windows authentication is removed. I've done extensive testing on this and this didn't cause any problem at all.

Scheduling DTS packages

Many developers might schedule a DTS package by right clicking on it and choose "Schedule Package...". However, if the person who schedules the package does not have

SYSADMIN authority, the scheduled package might not run at all. Most failed scheduled DTS packages are due to the fact the security context of SQL Server Agent is not set up properly. That is, SQL Server Agent proxy account is not set up or is not set up

properly. If you view the job history, you will see the following error message:

Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed.

When a DTS package is scheduled, a SQL Server Agent job is created. It is a regular

SQLServerAgent CmdExec job that uses the DTSRun utility supplied by SQL Server. The owner of the job is the login ID used by the DTS scheduler. If the job owner is a

SYSADMIN, the package is run under the SQLServerAgent service startup account. Otherwise, the context used is that of the SQLAgentCmdExec proxy account, if one is enabled. I think this account is disabled by default in SQL 2000.

To enable SQL Agent proxy account, you need to open Enterprise Manager, expand the Management folder, right click "SQL Server Agent", and then choose Properties.

Once you get the SQL Server Agent property window open, click the Job System tab. In the last frame of the tab, you will see Non-SysAdmin job step proxy account. Uncheck the check box. Then click the

Reset Proxy Account

button. Fill in appropriate information. Apply the changes and you are good to go.

When a developer schedules a package now, the owner of the job is still the

developer. When the job / package actually runs, it is going to be run under

whatever proxy account you just defined.

To test this, create a test job against a test database and change the job owner to a login that does not belong to

SYSADMIN. You will notice if you run the job it will fail before you enable the proxy account. After the proxy account is enabled, the job should run as expected.

One word of caution here. Enabling the SQL Server Agent proxy account is potentially a security risk. Therefore, you need to choose the account properly. Also, for SQL 7, a local Windows account will be created during SQL 7 installation. This account ID is

SQLAgentCmdExec and this account will be used as the proxy account. You really don't have a choice of which account you want to use. In SQL 2000, you could use any account (Local or Domain) you want.

Viewing the logs of DTS packages

For debugging purposes, you can log the execution of DTS package. Logging a DTS package can tell a developer the status of each step within the package: the start time, end time, whether it is

successful, if not, for what reason, etc. You might notice that developers will not be able to view the package logs once they are taken out of

SYSADMIN.

The reason is simple. As we mentioned earlier, DTS packages are stored in the msdb database. Likewise, its logging data is stored in the

sysdtssteplog and sysdtstasklog tables within that database. To allow developers view the logging information, just add

db_datareader role of msdb to his/her login ID. That should take care of this issue.

Stopping a DTS package when it is running

Occasionally, for whatever reasons, some developers might want to kill a running DTS package during execution. If they try to stop the DTS execution within DTS designer, they will not get any feedback from SQL Server and the package would still be running.

When a developer tries to stop a running DTS package, s/he essentially is trying to kill a process. Only

SYSADMIN and Process Admin can do this. To allow a developer to stop a running DTS package, his/her login ID has to belong to the

Process Administrator fixed server role. Alternatively, they could issue

the KILL command to kill the DTS process.

I actually don't recommend you doing this on production server. Hopefully you have a testing environment and you could grant

developers ProcessAdmin right on the test server. Developers should do their testing and debugging on the test server and move the packages to production after a

thorough testing is done. They should not be able to stop a process on the production at will.

Summary

In this article, I talked about some issues related to DTS package management, provided the solutions and the rational behind them. Feel free to post questions in this article's forum. I am planning to contribute more articles to this site. So stay tuned.