I've just moved all my SQL Server 2005 databases to a SQL Server 2008 R2 server with the same domain. I am 100% confident that all databases are present and accounted for. I used sp_help_revlogin to get the logins from 2005 server to the 2008 R2 server. All logins were created successfully. I then ran the following:
ALTER USER [MYDOMAIN\MYACCOUNT] WITH LOGIN = [MYDOMAIN\MYACCOUNT]
The problem is, I am getting the following error in the SQL Server Error log:
Login failed for user 'MYDOMAIN\MYACCOUNT'. Reason: Failed to open the explicitly specified database [CLIENT:XX.XX.XX.XXX]
Error: 18456, Severity: 14, State: 38
I get this error even if I give the give the domain account sysadmin privileges on this box (which it doesn't have normally).
I query sys.dm_os_ring_buffers and I can see several RING_BUFFER_SECURITY_ERROR errors, with ErrorCode: 0x534, APIName: LookupAccountSID. This, as far as I know, is a problem resolving the login details against AD. Then why does the very same account work perfectly fine on the same server in all the databases that it's been permissioned for?
Any help greatly appreciated.