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.
Syntax: ALTER AUTHORIZATION ON DATABASE:: TO [Instance-Login]; Sample: ALTER AUTHORIZATION ON Database::DBLAB TO sa;