April 21, 2010 at 3:04 am
Hi,
I am having an issue on a server with SQL Server 2008. Our DBA team has a windows group which is granted sysadmin on every database server. This all works fine except for one particular server where it gives the following error message;
Cannot open user default database. Login failed.
Login failed for user 'DMG\shaycullen'. (Microsoft SQL Server, Error: 4064)
I have checked and the default database for that group is set to master. I can access the server through sql server authentication and I have dropped the domain group using this login and recreated again but to no avail.
Now here's the strange part: when I add my own domain login to the server and set it as sysadmin and the default database as master, my windows aunthentication works perfectly. I have also tried adding myself to another group and giving it sysadmin rights on sql server but windows authentication still fails.
Any help on this would be much appreciated.
Thanks in advance,
Shay
April 21, 2010 at 10:39 am
It turns out one of our Network Admins had decided to install Backup Exec on this same instance of SQL and then removed the databases again leaving the login BUILTIN\Users behind him.
These were over-writing the permissions the other groups were granting and their default database was a database that no longer existed. Removed this and all is working! Hallelujah!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply