When trying to execute Stored Procedures on a particular Database we got the following error:
The server principal “domain\user” is not able to access the database “dbXYZ” under the current security context.
The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘dbXYZ’. You should correct this situation by resetting the owner of database ‘dbXYZ’ using the ALTER AUTHORIZATION statement.
The Database was restored by a backup from Production box to a separate Dev box. I checked the Properties of the Database and the Owner property shows the Prod service account, which cannot be created on Dev machine.
So, we just need to change the Owner Name of the database by using “ALTER AUTHORIZATION” statement of sp_changedbowner system SP, as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [DatabaseName] GO -- Option #1 EXEC sp_changedbowner 'sa' GO -- OR-- -- Option #2 ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [sa] GO |
And the error gone.
