Database Username and Schema do not match

[TimHayes]

I am using ArcGIS 10.2.1 and SQL Server 2008 R2.

I need to migrate a SQL Server Enterprise Geodatabase from one server machine (and Instance) to another server machine (and Instance). Let's call them Database A and Database B. Database A is the original SQL Server Enterprise Database and contains all the data. Database B is the new SQL Server Enterprise Geodatabase that contains no data. I want to move or copy the Feature Classes/Datasets in Database A to Database B.

I have tried Import, Copy/Paste, Exporting Feature Classes to a File Geodatabase then importing them into Database B, nothing seems to work.

I keep getting the same error message which basically says "Database Username and Schema do not match".

Any ideas on how to do this?

[NidhinKarthikeyan]

1. Take a complete backup of the Geodatabase (Through SQL Server).
2. Install SQL Server 2008 R2 on the new machine. (If you have SQL Server 2008 R2 installed on new machine, then skip step 2 and Restore the database).
3. Restore the database. (The one which Ii had mentioned in step 1).
4. Re-synch SQL Server logins

[WilliamCraft]

Nidhin's workflow is a good alternative.  Regarding the error you cited, I am wondering if the instance hosting Database B contains the same logins as with your instance hosting Database A.  I am also curious if you have set up all of the same user accounts and schemas in Database B as you have with Database A.  If not, that may explain the error you're seeing.

[TimHayes]

The schema is dbo for both databases. I cannot reinstall SQL Server. It is owned by our IT Dept. I do not have the permissions.

Database A in SQL Server Management Studio, under Security-Users-Role.Membership, I am the db_owner. This is checked.

In Database B, the db_owner is not checked. In role membership nothing is checked under my user name.

This could be the problem.

Unfortunately, our IT Dept controls roles for Database B. Whereas I control roles for Database A.

[NidhinKarthikeyan]

1. Take a complete backup of the Geodatabase (Through SQL Server).
2. Install SQL Server 2008 R2 on the new machine. (If you have SQL Server 2008 R2 installed on new machine, then skip step 2 and Restore the database).
3. Restore the database. (The one which Ii had mentioned in step 1).
4. Re-synch SQL Server logins

I would suggest you to try Step 3 and Step 4 in the new machine. I understand that SQL Server is installed in the new machine. You can go ahead and restore the database and Re-synch SQL Server logins.

[TimHayes]

I would suggest you to try Step 3 and Step 4 in the new machine. I understand that SQL Server is installed in the new machine. You can go ahead and restore the database and Re-synch SQL Server logins.

Database A has data in it; Database B is empty, no data; but it is enabled as an Enterprise Geodatabase. I was under the impression if I create Database B and enable it that I could simply copy or import the data from Database A into B. Looks like it is much more complicated than that due to each being on a separate Instance. However, I have multiple databases under the same Instance  on the same Server Machine as Database A and can copy and import across each of them no problem. The problem is that Database B is on a separate Server Machine and separate Instance. All use dbo schema and not sde schema with Windows Authentication. 

I will need to move heaven and earth to get our IT Dept to do what you are asking. It is not possible. So, it looks like I am out of luck. I will stick with keeping my data in Database A. This is not a problem. Thank you for the help.

[AsrujitSengupta · Accepted solution]

Tim,

The error comes up when the Schema does not match with the username for that Login

In SQL Server Management Studio--> Connect to Instance--> Instance Security--> Logins --> R-Click on the Login being used to copy over the data --> User Mapping --> Confirm that the 'Default Schema' matches the username!

If the user is part of the "sysadmin" role under the "Server Roles" we don't need this, otherwise the Default Schema and User should match. Microsoft grants 'dbo' automatically by Default, and we need to change that manually.

[davidbaron1]

Asrujit,

I am connected to the database in catalog as the sysadmin user. On top of Sysadmin, I've ensured that the default schema is identical to the name of the user, just as you've stated to do. Despite this, when I copy data from one gdb and into my target, the schema is still dbo and not the default schema I've registered with the user I'm connected with. 

Ideas? 

Many thanks,

db

[Asrujit_SenGupta]

A user who has been granted the 'sysadmin' server role, will always use DBO schema.

If you want the data owner to be the username:

Remove SYSADMIN > match the Username and Default Schema in User Mapping > grant the necessary permissions needed for creating\loading data.

[davidbaron1]

Thank you for the quick reply.

Using a separate sysadmin account, I removed the sysadmin role from the user I wish to use. Continued to navigate to instance>security>user>user mapping -- edit the user and default schema fields on the database I'm working on   > error: Rename fialed for User 'dbo' .... Cannot alter the user dbo