January 13, 2012 at 3:28 pm
I am re-introducing this issue since the older post had no workarounds or clues as to a possible source of the problem. Actually not sure if this is a problem but it certainly qualifies as unusual behaviour. The previous post was dated in 2009 and is referenced here http://www.sqlservercentral.com/Forums/Topic814963-1550-1.aspx.
When creating a Job or Maintenance Plan in SQL Server 2008, the Job Owner gets associated with the Windows Account with sysadmin privileges logged in at the time. Since the practice of associating routine server-oriented tasks with regular user Accounts is not recommended, changing the owner to either SA, or in my case, a service-oriented Domain Account is then carried out. Here's where the issue takes place.
When I select my other Account or "sa" for that matter from from the list of users, I get the message "No object was found with the name "[sa]", but objects with names containing "[sa]" were found. Select one or more names from this list of click Cancel to re-enter the name." each time regardless of the account selected.
As background info, the Database in question was recently restored using a backup copy from the same database running on SQL 2000. Scripts to preserve existing SQL 2000 Logins were not run beforehand or afterward. Accounts were manually created on the new 2008 Instance before the restore. I already tried the ALTER USER WITH LOGIN command to attempt to repair the suspect orphaned user but this did nothing to eliminate this issue. I'm not sure though if I used the correct syntax.
Has anyone experienced this issue lately or able to figure this one out yet?
January 24, 2012 at 2:57 pm
Understandable as to why no one replied before now. Looks as though the SQL User Interface is messed up. I opened a Support Case with Microsoft. They confirmed a coding problem and subsequently filed two Bugs for UI changes in the Select Login dialog box.
January 24, 2012 at 3:07 pm
Did you ever run sp_change_users_login 'auto_fix', '<login>'
Jared
CE - Microsoft
January 25, 2012 at 8:26 am
I performed EXEC sp_change_users_login 'Report' to determine if, in fact, there were orphaned users. The results were nil.
Nonetheless as I mentioned above, in order to completely eliminate what I suspected was an orphaned user, I also tried ALTER USER [DOMAIN\original_user] WITH LOGIN = [NEW_DOMAINew_user]. This basically allows remapping of a user to another login by changing the user's SID value to match the login's SID. This did nothing however to mitigate the odd behaviour exhibited when changing Job Owners.
As further indicated, Microsoft finally acknowledged (was a little painful) there were a few problems with their User Interface, more specifically, the Select Login dialog box functionality. Microsoft confirmed they will be addressing in the near future. :w00t:
Incidentally ALTER USER is now the preferred command for fixing orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins.
January 26, 2012 at 5:40 am
Interesting. Can you tell us what exact version you are on so in the future we know that there may be a bug in the version you are on and that a fix is being worked on for this issue?
January 28, 2012 at 6:39 am
The version we're running is SQL Server 2008 Service Pack 2 (10.00.4000.00) on Windows Server 2008 R2. This is affecting other versions as well. One other poster indicated he experienced the same issue with SQLServer 2005 (version 9.00.4035.00).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply