Sql-server – Windows authenticated users have lost access to master (default) database

sql server

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.

Best Answer

Ensure the guest account is enabled in the master database.

Related Solutions

Sql-server – Is the master database backup crucial for restoring MS SQL server in the event where you have to restore from backup and build database server from scratch

NOOOO, do NOT discontinue backup of master! Whereas most apps don't directly interface with the master database, master contains data for the current state of your server, databases, all kinds of things. That is horrible advice, to the point where I would seriously consider dumping their product. That really surprises me coming from a support agent of a backup product.

model is the template database, not master. master is crucial for your server to run properly (ie- at all). If something happens to it and you don't have a recent copy to restore from then your server is toast and you'll need to rebuild, reattach all of your db's, etc.

Sql-server – Windows authentication in SQL Server allows user to see ANY database

Sounds like Joe is a member of a domain group that has more rights on the SQL Server than you want, or you've been messing with the rights to the public roles in the other databases on the server. In either case you'll need to find out where the extra rights are coming from in order to fix them.

You can use the stored procedure xp_logininfo to see what domain groups Joe is able to access the database through.

exec xp_logininfo 'YourDomain\JoesUserName'

If that doesn't point you in the right direction you'll need to look into the rights granted to the public role in the other databases.

Best Answer

Related Solutions

Sql-server – Is the master database backup crucial for restoring MS SQL server in the event where you have to restore from backup and build database server from scratch

Sql-server – Windows authentication in SQL Server allows user to see ANY database

Related Topic