login - HasAccess property

Question

login - HasAccess property

sqltung

Hall of Fame

Points: 3962
More actions
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?

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

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

sqltung Hall of Fame Points: 3962 More actions · Answer 2

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.

Jared SSC Guru Points: 61793 More actions · Answer 3

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

Thanks,

Jared

Jared
CE - Microsoft

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 4

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]

Jared SSC Guru Points: 61793 More actions · Answer 5

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

sqltung Hall of Fame Points: 3962 More actions · Answer 6

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.

Jared SSC Guru Points: 61793 More actions · Answer 7

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

Jared SSC Guru Points: 61793 More actions · Answer 8

Also... Try running this statement:

USE DatabaseName;

GO

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

GO

Jared
CE - Microsoft

sqltung Hall of Fame Points: 3962 More actions · Answer 9

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.

Jared SSC Guru Points: 61793 More actions · Answer 10

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

sqltung Hall of Fame Points: 3962 More actions · Answer 11

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!

Jared SSC Guru Points: 61793 More actions · Answer 12

🙂 That's probably a very good idea!

Jared

Jared
CE - Microsoft

sqltung Hall of Fame Points: 3962 More actions · Answer 13

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..