locked
The server principal "xxxxxx" is not able to access the database "Dataxxxxx" under the current security context. RRS feed

  • Question

  • I have seen numerous post regarding this error and none of the solutions seem to work.  I'm gettting a little frustrated.  Server is Running Windwos 2008 R2 SQL 2008R2 RTM, I tried with SP1 as well.

    I receive the following error running a SQL Job:

    The server principal "xxxxxxx" is not able to access the database "Dataxxxxx" under the current security context. [SQLSTATE 42000] (Error 50000)

    I've tried local accounts, Domain accounts.  Granting db_owner to the account on the Dataxxxxx database but still nothing is working.

    I'm a SQL Newbie so please be as laymanesque as possible.

    Thursday, January 3, 2013 3:01 PM

Answers

  • The Account is NOT locked out.  I granted the account Admin privledges and logged into the server with that account and attempted to run the jobs - Still Failed.

    I've give the account DB_owner rights to the DB's (the one that contains the stored procedures and the one I'm attempting to update) - Still Failed.

    I changed the owner to the service account that runs SQL (also has the db_owner on the DB's) - Still Failed.

    Got it working.  It was odd and I don't quite understand why.  I removed the service account from the "Run as user" line in the advanced settings of the "Job Step Properties"   The job has run successfully every hour since Friday night.

    Monday, January 7, 2013 1:31 PM
  • Apparently this job makes access to other databases. When you impersonate a databaser user, you are sandboxed into the the current database. The sandbox can be opened, but this leads to security issues. If you have no good reason to use this Run as User, it should not be there.

    (I knew all the time that the error was due to impersonation, but I did not know where it happned, and in the screen shot you posted Run as User was blank.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 7, 2013 10:45 PM

All replies

  • I will try to be as laymanesque as possible. However, without actually working with you step by step to help you trouble shoot the issue; the next best thing is to offer a suggestion and literature for that suggestion. I suspect the issue to be related to SPN.

    http://msdn.microsoft.com/en-us/library/ms191153.aspx

    You can research this and let me know if you have any questions or you can wait for an MVP to reply.

    Frank.


    Frank Garcia

    Thursday, January 3, 2013 3:26 PM
  • No, I don't think this anything to do with SPN. This is an error that is internal to the server.

    So you have a job, and you get this message directly when you try to run it, or does the job fail on a statement in the job?

    How have you set up the job? Can you post a screen shot?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 3, 2013 10:46 PM
  • Erland,

    I stand corrected - This may be in the area of local rights.

    John, you are in good hands.

    Frank


    Frank Garcia

    Friday, January 4, 2013 1:37 PM
  • Whenever the job runs (either manually or scheduled) I receive the error listed above.  Here are the properties of the job.  The Job is running as the ID that runs all of the SQL services.

    Friday, January 4, 2013 2:41 PM
  • So the owner (the name you've blackened out) is the service account for SQL Server Agent and SQL Server? Does this account own other jobs that runs without problem?

    Is this the same account that appears in the error message?

    In the error message is the database the same as in the screenshot, or another one?

    Can you post the first couple of lines of the stored procedure? Does this procedure call sub-procedures?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 4, 2013 10:31 PM
  • Is the domain account locked or expired - or did the password change recently? try with sa job owner

    Manish

    • Proposed as answer by Woon Thong Monday, January 7, 2013 6:25 AM
    • Unproposed as answer by Woon Thong Monday, January 7, 2013 6:25 AM
    Sunday, January 6, 2013 7:26 AM
  • try this - set owner of the job to service account for sql agent. grant that account execute to the stored proc. Making that account db_owner will work too but probably too much permissions.


    • Edited by Woon Thong Monday, January 7, 2013 6:30 AM
    Monday, January 7, 2013 6:27 AM
  • John,

    What is the job running as?

    Is "server principal 'xxxxxxxx'" = SQL Server Agent Service account?

    Can you please let us know account in which SQL Server Agent Service account is running?

    What is the server permission of SQL Server Agent?


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Monday, January 7, 2013 12:36 PM
  • The Account is NOT locked out.  I granted the account Admin privledges and logged into the server with that account and attempted to run the jobs - Still Failed.

    I've give the account DB_owner rights to the DB's (the one that contains the stored procedures and the one I'm attempting to update) - Still Failed.

    I changed the owner to the service account that runs SQL (also has the db_owner on the DB's) - Still Failed.

    Got it working.  It was odd and I don't quite understand why.  I removed the service account from the "Run as user" line in the advanced settings of the "Job Step Properties"   The job has run successfully every hour since Friday night.

    Monday, January 7, 2013 1:31 PM
  • Apparently this job makes access to other databases. When you impersonate a databaser user, you are sandboxed into the the current database. The sandbox can be opened, but this leads to security issues. If you have no good reason to use this Run as User, it should not be there.

    (I knew all the time that the error was due to impersonation, but I did not know where it happned, and in the screen shot you posted Run as User was blank.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, January 7, 2013 10:45 PM
  • Removed the account from "Run as user" and left blank.. the job ran perfectly. thanks.
    Thursday, August 8, 2013 10:32 AM
  • We are migrating from SQL 2005 to SQL 2008 and we having the exact same problem, but we do not have the option to remove the account from "Run as user".  The job has to run as a specific user. 

    We added this user to these roles SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole but that did not help

    The stored procedure that gets executed by the job executes stored procedures in several databases.

    Any ideas on how to solve this without removing the account from "Run as user"?

    It worked fine in SQL 2005 

    Friday, August 30, 2013 4:56 PM