Aah, you're right, of course, Brian.  It just happened that TargetServersRole had been granted permissions to execute the DTS stored procedures on the server I looked at.

Greg

I don't know how removing foreign key constraints would affect performance, but it certainly would affect referential integrity between tables.  In my shop, data integrity is never sacrificed for the...

Try adding the developer to the TargetServersRole role in MSDB and grant EXECUTE permission on sp_add_dtspackage and sp_get_dtspackage to TargetServersRole.  You could also create a custom user role in MSDB...

Right.  In this case, Sysadmin is a server-level role in SQL Server.

Greg

You could put an owner password on the package and tell the developer what the password is.  To add the password you'll have to do a "save as" and give...

Matthew,

As Darrell says, the failure of your scheduled DTS packages sounds like a permissions issue.  When you schedule a DTS package, it run in the security context of the login used...

Query sysconstraints to get a list of constraints in a database.  See sysconstraints in BOL.

Greg

To turn off referential integrity, you have to disable or remove foreign key constraints.  Try running this in Query Analyzer saving the results to a file.  Then, run the file...

Run select serverproperty ('instancename') in Query Analyzer.  If the result is null, it's a default instance.  If the result is computer name\instance name, it's a named instance.

SQL Server 2000 allows...

Carl,

I don't know of any way to scan DTS packages and detect the use of a particular column.  I think you're stuck with looking at them manually.

Greg

It's a shared lock and is used for read only operations, such as a SELECT statement.  If an operation has a shared lock on a resource, another operation can also...

We've allowed developers to create packages that import test data into development databases from external sources (Access, text file) for a while.  As for packages that will be used in production...

Praveen,

To add to jarney's reply, the specific permissions you'll need to grant TargetServersRole are EXECUTE on sp_start_job and sp_stop_job.  The role had those permissions before sp3 was released.

Greg

I believe it's the default name that the DTS import/export wizard gives to a copy column transformation.  When you create a package in DTS Designer, the default is DTSTransformation__n.

Greg

There's also DBCC LOG.  It's undocumented and it's output isn't pretty, but it's free.  Here's an article about it: http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htm

Greg