Sql-server – Copy SQL Server DB into another instance without SA access

sql server

I've two SQL Server databases and both of them are on cloud.

  1. First server is source
  2. Second server is destination

I only have a non SA access. And I'm using DBForge Studio for managing the database. I'm trying to copy the database from the first server to the second server through DBForge→Database Sync→Copy Database and then select copy (I'm following this article).

Note:
The database already exists in destination with same name, but no tables yet.

I got an error message when trying to copy:

The EXECUTE permission was denied on the object 'xp_fileexist'…..

Then I try to copy & overide but I got the same error message.

So I try to use another way like export & import. I export the database from source server into .sql, but there are only queries for insert, it didn't serve the query for create table schema, and I tried to import into another file like .csv and .xsl but I got the same, it only contain data without schema.

Is there any better way to copy the database without SA access?

Best Answer

Is there any better way to copy the database without SA access?

Yes, you do not have to be sa or sysadmin member to perform the activity that you described, but following are the least privileges required to do so.:

You must be member of

  1. Database role: db_ddladmin or Database role: db_owner at source server
  2. Server role: dbcreator at destination server

You can verify current membership with following query (1=YES, 0=NO):

---- Source server
select  IS_MEMBER ('db_ddladmin' ) as Is_DDLAdmin, 
        IS_MEMBER ('db_owner' ) as Is_DBOwner
go

---- Destination server
select  IS_SRVROLEMEMBER ('dbcreator' ) as Is_DBCreator
go

I haven't used dbForge studio, but using SSMS free tool provided by Microsoft, and while having permissions (as mentioned above), you can achieve what your looking for (export data along with objects into .sql format)

via SSMS -> Tasks -> Generate script option. focus on following options at Advanced scripting options

  • Types of data to script = Data and schema
  • Script for server version = your desired version
Related Question