Reviewing Database Jobs and discovered a few failures:

The specific error read:

Sql Severity		16
Sql Message ID		33009
Message
'EXECUTE AS USER' failed for the requested user 'DBLAB\dbself' in the database 'DBSales'.  The step failed.</pre>
As the message was not very clear, launched a query window and tried the query:

Execute as login = 'DBLAB\dbself'

   Exec DBLAB.dbo.deallocate

revert

And, that worked.

Since this was not revealing, changed the query from ‘Execute As login’ to ‘Execute as user’

     Execute as user = 'DBLAB\dbself'

    Exec DBLAB.dbo.deallocate

    revert

We experienced an error when we attempted “Execute as user”. And, the error proved to be more useful and precise.

Msg 33009, Level 16, State 2, Line 1
The database owner SID recorded in the master database differs from the database owner SID recorded in database 'DBLAB'. You should correct this situation by resetting the owner of database 'DBLAB' using the ALTER AUTHORIZATION statement.

If you note, the same error message – “Msg 33009, Level 16, State 2, Line 1” – as when the Scheduled job runs.

It seems that we have a mismatch between the database owner registered in master and the one we had in the contextual database.

As we were not sure how to find the database owner in database itself, Googled for it and found a gem posting – R-Reid @ Strictly-Software.com blogged @ http://blog.strictly-software.com/2009/09/database-owner-sid-recorded-in-master.html

Basically, he blogged about using sys.database_principals to determine “Owner SID” in the current database.

use --database-

select

      tblDatabase.name as databaseName
    , tblDatabase.database_id as databaseID
    , tblDatabase.owner_sid ownerSIDInMaster
    , SUSER_SNAME(tblDatabase.owner_sid) as ownerNameInMaster

    , tblDatabasePrincipal.sid as ownerSIDInDatabase
    , SUSER_SNAME(tblDatabasePrincipal.sid) 
        as ownerNameInDatabase
    , tblDatabasePrincipal.owning_principal_id 
        as OwningPrincipalIDInDatabase
     , tblDatabasePrincipal.modify_date 
         as modifyDateInDatabase

from   sys.databases tblDatabase

 	  cross apply sys.database_principals tblDatabasePrincipal

where  tblDatabase.name = db_name()

and    tblDatabasePrincipal.name=N'dbo'

In retrospect, it seems that ‘Execute As user’ is a bit more restrictive than ‘Execute as Login’. And since ‘SQL Server Job Step’ uses ‘Execute as User’ one might experience errors such as this.

The need for “ALTER AUTHORIZATION ON Database:: TO ” is now a bit clearer.

Sample fix:

   Syntax:

     ALTER AUTHORIZATION ON DATABASE::
        TO [Instance-Login];

   Sample:

     ALTER AUTHORIZATION ON Database::DBLAB TO sa;