Windows authenticated users have lost access to master (default) database

Something very strange has occurred on our production SQL database. Users connecting via Windows authentication appear to have lost all access to the master database. By default, all logins have the default database set to master. So when you connect using SQL Server management studio, they get the error:

"Cannot open user default database. Login failed error 4064".

What's also worrying is that we have a group called "COMPANY - SQL Administrator" which has sysadmin rights and users in this group also get the same error. Worse, they don't appear to be system administrators anymore...

If they change their default database to something else, they can connect and then work on the database, it's just the master database that is problematic.

I'm not even sure by what mechanism windows authenticated users get access to the master database. Is it something hard coded in or some property that's got changed?

Any ideas?

Cheers, Rob.

From serverfault Rob Nicholson

• Have you made any changes in Active Directory lately? Does the COMPANY - SQL Administrator group have administrator privileges in other places, such as file servers or administration tools?

  It would seem that either the actual rights in AD have been screwed up, or something changes in SQL servers security scheme. Finding out which of those two is going on would be the first thing I'd do.

  Rob Nicholson : You are probably barking up the right tree there. An update was applied at the weekend. KB970892 was installed (Security Update for SQL Server 2005 Service Pack 3). This update has been failing to install for a while and the fix was as covered in: http://davehope.co.uk/Blog/kb970892-fails-to-install - a previous upgrade script was written with long-passwords in mind. So yes - something obviously has changed permission wise

  Rob Nicholson : Bit more information. I'm a member of "Company - SQL Admininstrator" and this group has sysadmin rights. But I am unable to do sysadmin stuff, e.g. create a database. If I temporarily add myself to the local administrators group on the W2k3 server, I gain sysadmin rights. However, I infer this is via the "You are a member of local administrators therefore you as a SQL sysadmin" and not by checking my membership of the AD group

  Rob Nicholson : Thinking about running this http://msdn.microsoft.com/en-us/library/ms143269(SQL.90).aspx - thoughts?
  From Tony Peterson

• Ensure the guest account is enabled in the master database.

  Rob Nicholson : The guest account is listed under Security and I've compared the settings there with the development server (where the problem does not occur) and they appear identical. I'm not 100% sure what you mean by "enabled". Is simply having Guest addded under security enough?

  Sam : It should be present and not have a red arrow in it. I doubt this is the issue - if guest were disabled, no one would be able to get into the server.

  Rob Nicholson : No red arrow shown so I think guest is enabled. I've also just discovered the exec sp_helpsrvrolemember command which does show that COMPANY - SQL Administrator is a member of this role. I suspect that the loss of sysadmin rights is a side effect of all users not been able to access the master database
  From Sam

• is dbo still set up on your master database?

  Rob Nicholson : Yes dbo user is still there with owner access
  From Thirster42