Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kwilliams
194 Posts |
 Posted - 2004-10-29 : 10:20:45
|
| I'm trying to export a database from one server running Windows NT w/ IIS 6.0 to another running Windows 2000 w/IIS 6.0, and I'm running into problems. This is the error message that I receive when trying to use the DTS Import/Export Wizard:Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server[Microsoft][ODBC SQL Server Driver][SQL Server]CREATE PROCEDURE permission denied in database 'DATABASENAME'Has anyone seen this before? If so, how can I work around this? Thanks for any help.KWilliamsREMEMBER TO VOTE |
|
|
nbpatil
Starting Member
14 Posts |
Posted - 2004-10-30 : 06:00:33
|
| Hi,The server on which you are trying to Export the data using DTS, check Database Creation permissions for the connection you are using.Preferably use 'sa' or any other user which has permissions for Table Create/Delete/Update/Insert etc.Nitin Patil |
 |
|
|
kwilliams
194 Posts |
Posted - 2004-11-01 : 09:59:19
|
| Hi nbpatil,Thanks for the response. Our Network Administrator checked that option already, and he received the same error message with the 'sa' option. That's what's so confusing about this problem. It acts like a Permissions error, but it works in the same way no matter what permissions are set. Any other suggestions? |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-11-01 : 14:01:41
|
| I would guess from looking at the error that it is to do with a stored procedure. Do you maybe have one that is not owned by dbo? Or one that is already on the destination database? Have you tried any other ways of copying the database?It's not wise to use sa (see plenty of other posts elsewhere), you would be better using another login with all the required permissionssteveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
kwilliams
194 Posts |
Posted - 2004-11-01 : 14:19:29
|
| Hello elwoos,I started out this process with using my own DB account, which has all access permission rights. To copy the DB, I've tried using the 'DTS Import/Export Wizard' and the 'Copy Database Wixard'. The 'Copy Database Wizard' won't even get through the dialog box on the 3rd "Select a Destination Server" step because it gives this error when the new server's name is entered: "Cannot connect to the server NEWSERVERNAME. Please check the server name and try again". So I'm pretty lost on what to do now. Any other suggestions would be greatly appreciated. Thanks. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2004-11-01 : 18:19:02
|
| I would say that it sounds from that as though you can't authenticate to the destination server. Does your login for the destination server have the same rights as for the source one? Can you 'see' the destination database from Enterprise Manager or Query Analyser? i.e. Can you access the database objects using either of these tools?steveTo alcohol ! The cause of - and solution to - all of life's problems |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-01 : 22:55:12
|
| 1. check all user permission if there are no explicit deny.2. if dts is saved as a package, check the owner of the package if it has enough permission (atleast db_ddladmin)3. try exporting one object only and check if the error is the same--------------------keeping it simple... |
|
|
|
|
|
|
|
|
DTS Import/Export Wizard Problems...