February 4, 2008 at 3:57 am
Right now we're on SP1. We haven't been able to upgrade to SP2 due to space issues (which we're getting resolved shortly).
Maybe that's the difference here. It'll be interesting to find out if our jobs that are currently group owned look for new owners when we upgrade.
February 5, 2008 at 10:05 am
You should create a sql account that only handles job ownership. give it the correct permissions (a scaled-down sa account).
if your domain controller goes down your job will still run because it will only need to authenticate locally.
Also, why would you disable the local administrators group on the server? Just curious.
Andy
February 5, 2008 at 10:13 am
Hi Andy,
At the moment the jobs are running under the sa account. I may at some point change them to a scaled down account. I was more curious as to why sql couldnt determine the owner as myself, and also why some people seem to be able to add groups as job owners and other cant.
Local admins are not disabled, they just have public access rather than the standard sysadmin rights. Purely to stop the network admins from doing too much damage on sql.
February 5, 2008 at 10:20 am
Ha! That's great. Usually the network admins are worried us DBAs are going to cause server trouble! Interesting.
Hopefully the issue will be resolved with upcoming SPs.
Good luck,
andy
February 5, 2008 at 10:34 am
If you are running with mixed authtentication enabled, just switch the job to run as SA.
This also prevents you from having issues with domain authentication.
February 5, 2008 at 10:52 am
The above posts bring up some good points. You have to weigh your domain needs against the job needs.
The reason my workplace uses domain group ownership of the jobs is that frequently our jobs need access to domain shares, files or server applications that are not available on the local server. It's easier for us to manage our domain security by using groups and if we use the group (plain vanilla domain group, btw) as the job owner, then we know the job will have the permissions it needs to access the appropriate shares. Plus we don't have to keep changing the job owner every time a member of the group leaves the company.
That's the one thing that irks me about SQL Server 2k5, BTW, is they went to all that work to fix the object ownership problem by creating schemas, but jobs are still owned by logins not schemas. I understand why it's still that way, I just wish they had thought about it when they were revamping the whole ownership model.
Anyway, if you don't have to worry about job domain access and you're using Mixed Auth, by all means switch the jobs to a SQL Login specifically created for the task and you shouldn't have to worry about it again.
February 14, 2008 at 10:07 am
This is exactly what i am seeing. My windows login WILMSLOW\johnmo is in a security group called wilmslow\#DBA, and that group is a sysadmin. The group does not have domain admin rights or local admin rights. My account has local admin rights explicitly.
The sql agent account (and service account) run as wilmslow\syssql which is a domain admin, and is not explicity granted access in SQL. The SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER login group is a sysadmin and has not been altered in any way.
My jobs were setup by myself with Wilmlsow\johnmo as the owner, and were happily working for the last month until last night after a reboot none of them would start. All said The job failed. The owner () of job TestDataRecieved does not have server access. So to get them running i changed them to sa, as it was the early hours of the morning and after such a long day i just needed sleep.
As BeachDBA says, if i now change the owner back to wilmslow\johnmo, they work fine, i just dont want to take the risk that they might all stop again so im leaving them as sa.
If it matters im running Standard Edition SP2 no CU applied.
I had the same problem and it was cured via a restart of SQL Agent.
February 14, 2008 at 10:11 am
I had the same problem and it was cured via a restart of SQL Agent.
Wait until you reboot the server again, its hit and miss whether the problem reoccurs :angry:
February 21, 2008 at 6:08 pm
HI ,
When the job runs under the security context of SQL Agent, why is the owner of the job requires Sysadmin access?
January 21, 2009 at 1:08 pm
you coul try giving to the account builtin/administrators only public access , maybe the issue is that your account has the privilegies but it first searchs the group privilegies in this case builtin/administrators and determines it has no access to the server , but then it doesn check permisions at user level so id doesn give you acces to execute it !!!!. So you could try giving acces public to the group (builtin/administrators )just to ensure the connection.
January 22, 2009 at 2:32 am
chr_koffe (1/21/2009)
you coul try giving to the account builtin/administrators only public access , maybe the issue is that your account has the privilegies but it first searchs the group privilegies in this case builtin/administrators and determines it has no access to the server , but then it doesn check permisions at user level so id doesn give you acces to execute it !!!!. So you could try giving acces public to the group (builtin/administrators )just to ensure the connection.
good idea, and i would try giving that a go, but ive moved on from the place this was happening at and not had the problem at my current work. it was very odd though!
June 24, 2009 at 1:18 pm
Cycling the SQL Agent service fixed this issue for me. Nothing about the account had changed since the jobs that this accunt owned had changed. Other jobs owned by other domain users succeeded fine.
STrange.
July 25, 2012 at 2:24 pm
I just got the same error.
I had created jobs perform backups and I'm the owner.
I'm backing up to the local machine.
I'm sysadmin and all of I sudden the job fails.
I switch the owner to SA and it works.
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 26, 2012 at 4:25 am
Welsh Corgi (7/25/2012)
I just got the same error.I had created jobs perform backups and I'm the owner.
I'm backing up to the local machine.
I'm sysadmin and all of I sudden the job fails.
I switch the owner to SA and it works.
Any ideas?
Sounds like your login doesn't have Share access permissions. Or at least not the proper ones.
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply