We have a DTS package that refreshes our external website data on a
nightly basis. I recently moved our external website off of our
domain. The SQL Services were previously running as
DOMAIN\Administrator account. When I moved the server off of the
domain, I reinstalled SQL Server and set the services to run as a
local, non-administrative account, MACHINE\sqlservices. The reason I
did this is, if someone were to compromise the machine via the SQL
Services, they would not have administrative access to the machine.

Now, my DTS package fails. I have been reading many posts on the
Internet to try to understand why the package fails, but I have not
been able to figure it out. When it fails, I am logged into the
Internal machine as DOMAIN\Administrator. The owner of the package is
also DOMAIN\Administrator.

Step 1: (succeeds) - sp_UserKill. This procedure kills all SPIDs with
the specified database (DATABASENAME). It appears to be successful.

Step 2: (fails) - drop database DATABASENAME. This fails with the
following message: "Cannot drop the database 'DATABASENAME' because it
is currently in use." Checking enterprise manager, it does not appear
to be in use. I've cycled the SQL Services and rerun with no luck.

I have a feeling that this is an issue with security, but I can't
figure it out. Can someone please assist me?

Thank you in advance.

Jeremy

Okay, I figured it out (right after I made my post). The issue was
that my connection in the DTS package was connecting to the database
that I was trying to drop. I changed it to default to the master
database and it worked fine.

Thank You,

Jeremy

jbi... (AT) sppinc (DOT) net wrote: