1

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.

1 Answer 1

0

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.

3
  • Amazing, this actually worked... I've been clicking around and getting frustrated at the username being greyedout when opening up the properties. As your explanation is what I was thinking the SID was the same, but the name is just different. So changing the name should be fine. I never thought that F2 would allow me to change it, but it did.
    – AlexR
    Commented Jan 14, 2015 at 17:04
  • That's cool that it actually worked as expected. I think I'll heat up a test system and see what it's doing behind the scenes with Profiler.
    – db2
    Commented Jan 14, 2015 at 17:56
  • Looks like it's just ALTER LOGIN [test] WITH NAME=[testlogin], and likewise with ALTER USER. It'll whine if you try to change an NT login/user to one that it can't find in the directory, though.
    – db2
    Commented Jan 14, 2015 at 18:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.