September 16, 2011 at 8:54 am
We recently made a change to our usernames moving from Novell to Active Directory. I manage my own domain which SQL Server is a member of. In order to create the trust between domains, I had to change my username from jpowers to jim.powers in my domain. I'm able to log into SQL Server using my new domain login name of jim.powers and I have all of my rights (sa).
The problem is every time I try to set something up, especially a new job, it throws errors. This is one specifically from a job:
The job failed. Unable to determine if the owner (PINNACLE\JPOWERS) of job SQLPROD\PINNACLE-Pinnacle-Pinnacle-SQLPROD\IT-49 has server access (reason: Could not obtain information about Windows NT group/user 'PINNACLE\JPOWERS', error code 0x534. [SQLSTATE 42000] (Error 15404)).
I can't figure out how to tell SQL Server that my username has changed. I have so many objects that I own in the server, I can't just delete my user and re-create it. How to I tell SQL Server that I'm no longer PINNACLE\JPOWERS and that I'm PINNACLE\jim.powers?
September 16, 2011 at 9:50 am
I think this will get you in the right direction.
This will help with the tables.
The jobs is more of a manual process.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2011 at 11:50 am
Is there a way to modify my login to change it from jpowers to jim.powers? That would solve the problems since it would be able to find me in AD again.
September 19, 2011 at 4:11 am
maybe you have checked but have a look at the job owner. Change that to your new user name or if possible to sa.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 19, 2011 at 7:18 am
When I change the job owner to sa, it works fine. Problem is that when I create new replication publications or subscriptions, it defaults to me being the owner. I really need to know how to change my username from jpowers to jim.powers.
September 20, 2011 at 12:10 am
hi
Does this happen with new jobs? I have seen this with maint plans if they are updated but not when you update the job self.
In which senarios does the owner change to the old user?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 20, 2011 at 1:23 am
hi
Workaround that can be used is to create a trigger on the MSDB to update sysjobs table when record created or updated.
update [msdb].dbo.sysjobs
set owner_sid = 0x01
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 20, 2011 at 7:13 am
It occurs whenever I adjust maintenance plans and add new publications/subscriptions.
Is there really no way to change my username?
September 20, 2011 at 12:41 pm
You can change your login name via t-sql. Heres a link on it.
http://msdn.microsoft.com/en-us/library/ms189828.aspx
Adam Durr
Remote DBA support
www.bluegecko.net
September 21, 2011 at 1:11 pm
Awesome, don't know why I couldn't find that one. It worked!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply