Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Is the database in MULTI_USER mode? –  DanBig Jun 11 '12 at 19:41
    
The master database can't be set to single user mode. –  mrdenny Nov 15 '12 at 2:41
add comment

3 Answers

Ensure the guest account is enabled in the master database.

share|improve this answer
    
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? –  Rob Nicholson Sep 16 '10 at 15:50
    
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. –  Sam Sep 16 '10 at 15:57
    
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 –  Rob Nicholson Sep 16 '10 at 16:11
add comment

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.

share|improve this answer
    
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: 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 Sep 16 '10 at 15:54
    
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 Sep 16 '10 at 15:59
    
Thinking about running this msdn.microsoft.com/en-us/library/ms143269(SQL.90).aspx - thoughts? –  Rob Nicholson Sep 16 '10 at 16:20
add comment

is dbo still set up on your master database?

share|improve this answer
    
Yes dbo user is still there with owner access –  Rob Nicholson Sep 17 '10 at 15:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.