I have an odd situation and I hope someone can help me get out of this.
The situation:
I'm running an Azure SQL 2012 SP2 VM with Windows Server 2012. Since it's creation I promoted the server to be a domain controller and everything is fine. I've also installed an application (Dynamics CRM) which is pretty heavy on its database use (multiple databases, a few scheduled jobs).
I'm getting an error on these scheduled jobs saying that they can't validate whether computername\my.username has access.
SQL Server Scheduled Job 'MSCRM_CONFIG.SiteWideCleanup' (0x1D1BA70291EB7E44810A6121F9045F17) – Status: Failed – Invoked on: 2015-01-14 00:30:00 – Message: The job failed. Unable to determine if the owner (SYSTEMNAME\my.username) of job MSCRM_CONFIG.SiteWideCleanup has server access (reason: Could not obtain information about Windows NT group/user 'SYSTEMNAME\my.username', error code 0x534. [SQLSTATE 42000] (Error 15404)).
It looks like, when I promoted my server to a domain controller, my SYSTEMNAME\my.username object got changed to DOMAINNAME\my.username. I can log in to SQL with my domain username and have all the appropriate privileges (DBOwner), but when looking at the "Login" section I still see the reference to SYSTEMNAME\my.username.
I've been searching for a way to change the user in SQL around, but haven't been able to find an answer (I'm guessing this is a very odd situation that shouldn't happen when someone sets things up properly) and hope to find one here:
How can I switch the SQL Server login SYSTEMNAME\my.username over to DOMAINNAME\my.username without losing all the things that this username is attached to?
Note: this is a development server that I set up just for my own fiddling purposes, and I can do whatever I'd need to even if it's not best practice under normal circumstances.
Best Answer
Windows logins are tied to Windows users/groups based on the SID. In other words, you're still able to log in since the Windows user's SID matches, even though the login name still indicates the system name rather than the domain name. If you're worried about the mismatch, you can drop and recreate the login. Since it'll have the same SID, it should map to all the existing database users, and privileges will be preserved. Also, Management Studio seems to think you can hit F2 to rename a login, but I don't have a test system handy to confirm that works.
Of course, all the database users will still have the old name too. You can either script/recreate them, or try the F2 method.
After you've done all this, you'll probably need to change the owner on the jobs. I usually keep all the jobs owned by SQL Server logins (typically sa) so that they don't all fail if there's a temporary problem with the DC.