0

I'm struggling with a SQL Server Agent Job run time error:

SQL Server Agent Job - Runtime error The server principal "ABC" is not able to access the database "XYZ" under the current security context. [SQLSTATE 08004] (Error 916). The step failed.

I know there are similar questions here but not necessarily related to SQL Server Agent Jobs ..

I have reviewed various web articles but not yet succeeded in resolving ..

The server principal is not able to access the database under the current security context https://www.sqlservercentral.com/forums/topic/the-server-principal-is-not-able-to-access-the-database-under-the-current-security-context-urgent

The server principal is not able to access the database under the current security context in SQL Server MS 2012

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/overview-of-sql-server-security

Am running job as a specified user IAM-Replication.

Allan F
  • 2,110
  • 1
  • 24
  • 29

1 Answers1

1

try to create a database user for the login/server principal and grant "enough" permissions to see if the job works.

--The server principal "ABC" is not able to access the database "XYZ" under the current security context. [SQLSTATE 08004] (Error 916). The step failed.

use XYZ --<-- put the db name here
go

--create a database user for the server principal "ABC"
create user dbuserforABC for login ABC
go

--overly grant permissions to dbuserforABC
alter role db_owner add member dbuserforABC --better revise the dbuser permissions, use this just for poc/troubleshooting
go

--try the job again
lptr
  • 1
  • 2
  • 6
  • 16
  • I'm not sure what to do when this doesn't work and I still get this same error / issue? Maybe a sql*server 2017 bug / issue ? – Allan F Jan 13 '20 at 00:18
  • If I google for "SQL Server Agent Job - Runtime error The server principal is not able to access the database" I get half million results ... so this must be a commonly occurring issue. – Allan F Jan 13 '20 at 00:20
  • I spotted this article : https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c874e3d-b39d-494e-be12-e3d72c3a73b2/the-server-principal-quotxxxxxxquot-is-not-able-to-access-the-database-quotdataxxxxxquot?forum=sqlsecurity likewise .. If I remove the "run as" in the job then it works .. hmmm ... I had tried all the same heavy handed security steps also .. – Allan F Jan 13 '20 at 00:25
  • 1
    the accepted answer in the article you have spotted provides the reason/explanation why you get the error. When you set the "run as" this equates to : execute as user = " IAM-Replication" (== impersonation). When you impersonate a dbuser, you can work only in the context/scope of one/single database. If you try to access another database (or execute a module on another database) you get the infamous: "The current principal cannot...under the current security context" error. Current security context=impersonation. – lptr Jan 13 '20 at 08:04
  • 1
    you can use impersonation (run as when talking about an agent job) and access another database (from the current database) if you ALTER curerntdb SET TRUSTWORTHY ON which is a "BAD" security practice and you really do not have to do that. You could try testing with TRUSTWORTHY and check if it works (for educational reasons), however, you should ask: Do i need to impersonate/run as IAM-Replication? Most likely you do not need to. – lptr Jan 13 '20 at 08:11