Post reply

login - HasAccess property

  • September 15, 2011 at 8:50 am

    #248086

    I've got an agent job that won't run.. gives me this error:

    The owner (MyDomain\warehouse) of job Load_Job does not have server access.

    This is on a new dev box running Windows Server 2008 and has SQL Server 2008 R2. Our production box has the same login, and the job is running ok.

    I found a property of the login under the "facets" menu called HasAccess. It is set to 0, whereas most of the other logins are set to 1. The setting is greyed out.

    Does anyone know how to change the setting, or what would be responsible for setting this?

  • September 15, 2011 at 9:41 am

    #1382070

    Try double-clicking the user in the Security\Logins section of SSMS and then go to their status. Make sure this user has the proper permissions here.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 15, 2011 at 10:40 am

    #1382133

    Yup, everything's fine there, and same as on production box. Enabled property is set to true, but that's not the same as the HasAccess property, which is still false.

  • September 15, 2011 at 10:43 am

    #1382139

    Hmm... Have you checked the domain permissions for that user? Maybe this is not an SQL configurable piece.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 15, 2011 at 10:46 am

    #1382143

    Does Job Owner and Job was invoked by the same user ?

    If not I would really change the owner of the job to sa or some system account.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • September 15, 2011 at 10:49 am

    #1382146

    I would try deleting the user from your dev instance of SQL Server and readding it.

    EDIT: Check this out as well http://www.databasejournal.com/features/mssql/article.php/3739386/Policy-based-Management-in-SQL-Server-2008-150-Part-I.htm

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 15, 2011 at 11:15 am

    #1382162

    Jared, where would I look to check the domain permissions? That sounds promising, but the job is owned by the same user on production and is running ok. Thanks for the link, I'll check it out.

    @sqlfrndz, Job owner is not the same account as the account used to start Agent, but it is setup the same on the production server and is working ok.

  • September 15, 2011 at 11:19 am

    #1382165

    Domain permissions are best handled by working with your your system or domain admin. I still think, though, that it could be something to do with the SQL user. Was this user created on this instance, or imported with the database? Have you check the user mapping to the databases?

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 15, 2011 at 11:23 am

    #1382168

    Also... Try running this statement:

    USE DatabaseName;

    GO

    EXEC sp_change_users_login 'Auto_Fix', 'MyDomain\warehouse';

    GO

    Jared
    CE - Microsoft

  • September 15, 2011 at 11:35 am

    #1382180

    Jared, the main dba in our group runs a script which re-maps any un-mapped logins to db users for all databases. I've seen a number of similar scripts here.

  • September 15, 2011 at 11:45 am

    #1382187

    All article with reference to HasAccess refer to a login being granted access to login to SQL Server. This leads me to believe something is wrong with the SQL login. If everything "looks" correct and you cannot figure out what is wrong, I say delete the user and add it back. I no longer believe that it is a domain policy issue, but SQL Server.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • September 15, 2011 at 12:01 pm

    #1382197

    Jared, I'll have to give that a shot. But first, I need to make sure I won't cause more problems by deleting and re-adding that login!

  • September 15, 2011 at 12:33 pm

    #1382209

    🙂 That's probably a very good idea!

    Jared

    Jared
    CE - Microsoft

  • September 15, 2011 at 12:37 pm

    #1382212

    so one of the DBAs tried using EXEC master..sp_grantlogin 'mydomain\login' and this has fixed the problem.

    I didn't setup the server or the logins, so I'm not sure exactly how it was done, but I guess we're ok for now. The job is starting, now have to debug another error..

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply