Saturday, June 16, 2007 9:12 PMI have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:
The server principal "FREEDOM1\FreedomAdmin" is unable to access the database "master" under the current security context.
If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?
Sunday, June 17, 2007 12:09 AMActually, it doesn't matter whether the default database is "Freedom" or "master". I get the same error either way.
Monday, June 18, 2007 4:10 PMNot normally - the public group has read access to master, so that they can log in and so on. Someone must have messed with the permissions on your master database.
Monday, June 18, 2007 11:06 PMYea! I found out I could log in if I changed my security to sysadmin. Is there a way to reset the security for public to the default? If not, do know what its security should be set to? Thanks.
Monday, June 18, 2007 11:47 PMModerator
Typically there should not be any permission explicitly denied to public. The following query can help you find if any permission has been denied at the server scope:
SELECT * FROM sys.server_permissions WHERE grantee_principal_id = 2 AND state = 'D'
The permissions granted out of the box to public should be VIEW ANY DATABASE and CONNECT to the default ENDPOINTs.
Let us know if this helped or if you have any additional questions.
SQL Server Engine
Tuesday, June 19, 2007 2:11 AMconnect to master database had been denied. I granted such permission and my problem went away. Thanks.