Jennifer_Jinhong_34

Permission Error when upgrading MSSQL database

Discussion created by Jennifer_Jinhong_34 on Mar 2, 2016
Ran into this issue when trying to update my 11.2.0 db to 11.2.1.
Figured others might run into the same problem and could use this as a reference.

The DB.Load fails with the message:
20160301/235151.266 - U00038077 Do you want to ignore this error?

Please note: Do not continue without contacting Automic Support!
20160301/235155.087 - U00038245 If you cancel the Database will probably be in an inconsistent state. Please contact Automic Support to evaluate the best approach. Do you really want to cancel?
20160301/235422.953 - U00038077 Do you want to ignore this error?

Please note: Do not continue without contacting Automic Support!


In my scenario, I copied the database from my test environment on a different server. All the configurations and naming conventions were the same between the 2 environments.

  • Same DB users
  • Same DB name

At the beginning of every load, a permission check script (check_privileges.sql) is ran to ensure the db user has the rights to run all the update statements. (Create Table, Create Index, Alter Table, Create Procedure, Exec (sproc) )

What ended up being the cause was that the database's owner_sid didn't match the databases db_owner sid. (This was because I had copied the database from a different server.)

This is what I did to verify and resolve the issue:

SELECT owner_sid FROM sys.databases where name = '<db name>'; SELECT sid from <db name>.sys.database_principals WHERE name = 'dbo';
The results would be different SIDs.
To resolve the issue I did the following. That reset the db_owner the correct SID for the user in this environment.
Use <db name>;
DROP USER <user>;
ALTER AUTHORIZATION ON DATABASE::<db name> TO [<user>];

Outcomes