July 14, 2006 at 11:16 am
We have a DTS job that copies data from a test db to a production db. There are sql logins on each end and on the destination the login is in the dbo role. yesterday there was a hiccup and a bunch of tables were deleted in the production db. not a big deal to recover them, but we want to prevent it from happening again.
We took the login out of the dbo role on the destination server and the package failed to run. We get an error that we can't run alter database. which permissions should we assign to allow it to run, but prevent it from deleting tables?
July 17, 2006 at 8:00 am
This was removed by the editor as SPAM
July 17, 2006 at 10:39 am
Does the package create tables in the destination database or just insert data? If it's the former, the destination user must CREATE TABLE permission or be in the db_owner or db_ddladmin role. If it's the latter, just grant INSERT permissions to the tables that receive the data or make the user a member of db_datawriter.
Greg
Greg
July 24, 2006 at 12:57 pm
it's not set to drop tables, just replace existing data. I tried changing some options and still doesn't work. and giving create table or db_ddladmin doesn't work either. Only dbo.
The error is that it can't run alter database and sp_dboption
July 24, 2006 at 2:21 pm
What are the tasks in the package and what does each task do? Enable package logging and post which task fails.
Greg
Greg
July 25, 2006 at 11:18 am
Step Error Source: Microsoft SQL-DMO (ODBC SQLState: 42000)
Step Error Description:[Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to alter database 'WebDB'.
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
Step Error code: 80041393
Step Error Help File:SQLDMO80.hlp
Step Error Help Context ID:1131
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply