locked
Problem with permissions calling stored procedure in another database using qualified name RRS feed

  • Question

  • We have a job that runs stored procedure in database X. That stored procedure updates a table that is located in database Y on the same server. The insert and statements are qualified with the other database name: "insert into Y.dbo.tbl", etc.

    The job is owned by "sa". The job runs the stored procedure in a T-SQL job step. The job step has a "Run as user" "dom\xyz". The User is a Windows User that is in the db_owner role in database X. There is a corresponding User in database Y that is also in the db_owner role. It is associated with the same Login as the User in database X.

    We recently upgraded from SQL Server 2005 to SQL Server 2008 (Microsoft SQL Server 2008 (SP1) - 10.0.2847.0 (X64)   Jul 13 2011 18:52:06   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)). The job was running fine on SQL Server 2005. On SQL Server 2008 we are getting a perrmission error *some of the time* saying:

    "Executed as user: dom\xyz. The server principal "dom\xyz" is not able to access the database "Y" under the current security context."

    We do not have "Cross database ownership chaining" enabled. Since the User in database Y has db_owner permissions, I would not think that we would need to do so.

    We have tried dropping the Users and the Login and re-creating them, so it is not a matter of a disconnect between the Login and the two Users.

    Ron Rice


    Ron Rice


    • Edited by r31416 Friday, March 2, 2012 12:20 PM
    • Moved by Tom Phillips Friday, March 2, 2012 9:11 PM Possibly better answer from Security forum (From:Transact-SQL)
    Friday, March 2, 2012 12:14 PM

Answers

  • Try changing the owner of the job to the domain account - which will for the job to run in the context of that domain account.  Because you have the job owned as 'sa' it is actually running in the context of the service account running SQL Server.

    In SQL Server 2008 - the service accounts no longer have direct access to the database system.  The service account is assigned to the service and a Service SID is created.  That Service SID is what now has access to the database system and that has sysadmin rights, but the actual service account does not.


    Jeff Williams

    • Marked as answer by r31416 Monday, March 12, 2012 10:42 AM
    Saturday, March 3, 2012 2:27 PM
  • Changing the owner of the job to the domain account did work. Thank you! It is not clear to me why that would make a difference, the job step was running in the context of the domain account because that account was "Run as user" for the job step. We know this for sure because the error message for the job step indicated that it was the domain account that supposedly did not have permissions. Also, when we removed the domain account from "Run as user" and let the step run in the context of the SQL Server Agent account, it succeeded.

    Ron, keep the domain account as the job owner, since this is the least privileged account. A T-SQL job step will run under the security context of the job owner when the owner is not a sysadmin. if the owner is a sysadmin, the job will run under the security context of the SQL Server Agent service account.


    SQL Server Database Administrator

    • Proposed as answer by Iric Wen Monday, March 12, 2012 9:18 AM
    • Marked as answer by r31416 Monday, March 12, 2012 10:42 AM
    Wednesday, March 7, 2012 1:09 AM

All replies

  • I enabled Cross database ownership chaining and ran the job, and it failed as before. I re-started the services just to be sure, and the job still failed with the same permissions error.

    Also, if I log on with the service account that is being used in the "Run as user" and run the stored procedure directly in Management Studio, it runs with no error.

    And if I remove the "Run as user" from the job step and let it run under the SQL Agent service account, which is in the Administrators Windows security group and therefore has sysadmin permissions on the SQL Server instance (not best practice, I know), the job runs.

    Ron


    Ron Rice

    Friday, March 2, 2012 3:08 PM
  • Hi,

    Sounds a little difficult for me to understand but I can provide you two tips maybe it helps:

    try to add before calling the procedure this:

    Use MyDb

    if that is not working then try to alter the authorization on both Db's to 'sa'

    alter authorization on database :: MyDb to sa

    Hope it helps somehow :)

    With regards,

    Muli


    If some code doesn't work, don't worry help is on the way.. don't forget to mark your thread as solved when done...

    Friday, March 2, 2012 3:43 PM
  • Hi!

    Could you possibly try setting the TRUSTWORTHY on upon the databases? I recall having a similar issue and all I needed to do was setting trustworthy on.

    HTH.

    Friday, March 2, 2012 6:03 PM
  • Sounds like you have covered all your bases. This may be hard to figure out but I have seen this problem before and here are a few things to check, 1. Do you have a triple hop issue with kuberos. example try executing the procedure from the server and not a client. you could be loosing credentials 2. do you have database triggers that fire on execution.  perhaps the trigger writes to a table that xyz does not have permissions too. 3. have you tried relinking the accounts very important as in alter user xyz with login = xyz.
    Friday, March 2, 2012 6:57 PM
  • Try changing the owner of the job to the domain account - which will for the job to run in the context of that domain account.  Because you have the job owned as 'sa' it is actually running in the context of the service account running SQL Server.

    In SQL Server 2008 - the service accounts no longer have direct access to the database system.  The service account is assigned to the service and a Service SID is created.  That Service SID is what now has access to the database system and that has sysadmin rights, but the actual service account does not.


    Jeff Williams

    • Marked as answer by r31416 Monday, March 12, 2012 10:42 AM
    Saturday, March 3, 2012 2:27 PM
  • Changing the owner of the job to the domain account did work. Thank you! It is not clear to me why that would make a difference, the job step was running in the context of the domain account because that account was "Run as user" for the job step. We know this for sure because the error message for the job step indicated that it was the domain account that supposedly did not have permissions. Also, when we removed the domain account from "Run as user" and let the step run in the context of the SQL Server Agent account, it succeeded.

    In any case, since this change worked we will keep it even though we do not fully understand it!

    Thanks again,

    Ron Rice


    Ron Rice

    Monday, March 5, 2012 1:50 PM
  • http://www.sommarskog.se/grantperm.html

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, March 5, 2012 2:07 PM
  • Changing the owner of the job to the domain account did work. Thank you! It is not clear to me why that would make a difference, the job step was running in the context of the domain account because that account was "Run as user" for the job step. We know this for sure because the error message for the job step indicated that it was the domain account that supposedly did not have permissions. Also, when we removed the domain account from "Run as user" and let the step run in the context of the SQL Server Agent account, it succeeded.

    Ron, keep the domain account as the job owner, since this is the least privileged account. A T-SQL job step will run under the security context of the job owner when the owner is not a sysadmin. if the owner is a sysadmin, the job will run under the security context of the SQL Server Agent service account.


    SQL Server Database Administrator

    • Proposed as answer by Iric Wen Monday, March 12, 2012 9:18 AM
    • Marked as answer by r31416 Monday, March 12, 2012 10:42 AM
    Wednesday, March 7, 2012 1:09 AM
  • I ran into this problem again when moving to a new server. This time, I changed the owner of the job to the domain account (domain\x) as instructed, but the job still failed saying the account it was running under (domain\x) did not have permissions (executing a stored proc that references a different database with dot notation in a T-SQL job step). I decided to see what would happen if I removed the "Run as user" (domain\x) from the job step. After I did that, it worked.

    So again:

    Job running in SQL Server 2005 with "sa" as job owner and "domain\x" as "Run as user" works fine.

    Job running in SQL Server 2008 with "sa" as job owner and "domain\x" as "Run as user" says "domain\x" does not have permissions.

    Job running in SQL Server 2008 with "domain\x" as job owner and "domain\x" as "Run as user" says "domain\x" does not have permissions.

    Job running in SQL Server 2008 with "domain\x" as job owner and no "Run as user" works fine.

    Ron Rice


    Ron Rice

    Monday, May 7, 2012 7:09 PM
  • Run this SELECT on both SQL 2005 and SQL 2008

    SELECT is_trustworthy_on FROM sys.databases WHERE name = 'yourdb'

    My guess that you will get 1 back on SQL 2005 and 0 on SQL 2008.

    Note: the TRUSTWORTHY setting is nothing to take lightly.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, May 7, 2012 9:37 PM