SQL Server – “Job Step” \ “Execute As” – Error – The server principal is not able to access the database … under the current security context. [SQLSTATE 08004] (Error 916)

Problem Identification

Not able to get the following options to work well with each other

  1. Job Step – General
    • Type – Transact-SQL Script (T-SQL)
    • Database – Let use say Database1
    • Command – SQL Statement that references another database other than the one referenced in database drop-down. Let use say Database 2
  2. Job Step – Advanced
    • Run as user – Specify SQL Login Account

Problem Diagnosis

  1. The problem is when the “Run as user” is specified, MS SQL Server basically invokes an “Execute as User” clause.  Ordinarily the “Execute as User” clause only has permissions on the current database context.
  2. The  error resembles the one pasted below:

Message


Executed as user: LAB\sqluser.
The server principal "LAB\sqluser" is not able to access the database "DBA" under the current security context. [SQLSTATE 08004] (Error 916). 
The step failed.

Problem Resolution

  1. To allow access to other databases:
    • Grant “Trustworthy” privilege to the originating database (ALTER DATABASE [database-name] SET TRUSTWORTHY ON;)
    • If the object that is being referenced in the other database is a Stored Procedure, then signing the object being referenced is also helpful

Addendum

Other Troubleshooting tracks

[ This part was added on Wednesday, June 8th, 2015 ]

This morning, krishna kumar commented — thanks for the post , Im trying to execute SP via job geting the Error 916 , what can be done in this situation ?

Here is my comment, there are a couple of other things one can try:

  • Use Execute as Login to review what happens behind the scene
  • Track activities using SQL Profiler

Execute as Login

SELECT 
          [SOURCE] = 'Before Execute as Login'
        , 'SUSER_NAME()' = SUSER_NAME()
        , 'USER_NAME()' = USER_NAME();

-- please replace login-name with the job's owner
EXECUTE AS LOGIN = [login-name];

--Verify the execution context is now the job's owner.
--Display current execution context.
SELECT 
          [SOURCE] = 'During Execute as Login'
        , 'SUSER_NAME()' = SUSER_NAME()
        , 'USER_NAME()' = USER_NAME();


-- revert back to your actual login
REVERT;

SELECT 
          [SOURCE] = 'After Execute as Login'
        , 'SUSER_NAME()' = SUSER_NAME()
        , 'USER_NAME()' = USER_NAME();

SQL Profiler

Launch SQL Profiler and track LoginName and SessionLoggingName

Couple of key points:

  • Events
    • Errors and Warnings
      • Exception
      • User Error Message
    • Security Audit
      • Audit Login
      • Audit Logout
    • Stored Procedures
      • RPC:Started
      • RPC:Completed
    • TSQL
      • SQL:BatchStarting
      • SQL:BatchCompleted
  • Columns
    • LoginName
    • SessionLoginName

 

ExecuteAsLogin-TraceProperties

 


Good Reading

 

5 thoughts on “SQL Server – “Job Step” \ “Execute As” – Error – The server principal is not able to access the database … under the current security context. [SQLSTATE 08004] (Error 916)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s