SQL with Manoj

 
Today we got an email from our support team that they are not able to execute Stored Procedures on a particular Database and getting 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:

USE [DatabaseName]
GO

-- Option #1
EXEC sp_changedbowner 'sa'
GO

-- OR--

-- Option #2
ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [sa]
GO

And the issue got resolved !!!