August 16, 2012 at 4:56 am
Hi,
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.
Andrew
August 16, 2012 at 7:29 am
sp_validatelogins doesn't return anything either
August 16, 2012 at 7:31 am
Run a trace for failed logins and see what database the failure is on.
Then check to see if the DB exists or if the user has access to the DB.
August 16, 2012 at 7:43 am
hi,
Already tried that. It shows the database for the failed login attempt as 'master'. I'm hoping that that's still there!
Cheers, Andrew
August 16, 2012 at 7:46 am
Does the user have access to master
Remembers you detailed error exists if grant SA rights.
What is the default database for the user? Does that exist?
August 16, 2012 at 7:50 am
the default database is master.
This account is a domain admin. When we were on 2005, although it had permissions explicitly granted at the database/object level, it was a member of the sysadmin role via builtin\administrators. Obviously in 2008 that's no longer the case. Through testing, we found that the account didn't require any further permissions, so it was left with the explicit permissions. The software that uses this account and connects to SQL Server doesn't report any errors. I can see activity from this account on each of the databases that it needs to have. I still have the old 2005 box, and comparing permissions, databases, etc. side-by-side, it looks identical.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply