Lowell (3/14/2013)
check the default database for that login;if the database was deleted, then you'll see that error, and need to reassign the default login, probably to master.
so as an example, if i create a login [ClarkKent] with a default database of [SandBox], and then later drop (or rename?) the database [SandBox], you would see this behavior.
the fix is fast and easy:
ALTER LOGIN [ClarkKent] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
Thanks Lowel for taking time.
I have checked the default_database for these users & it is already set to master, so that's not an issue.
Another thing I see, when I go to database-->Properties-->Permissions-->Username(actuly a windows group)
The effective permission tab provides this message :
"Cannot execute as the server principal because the principal XXX does not exist, this type of principal cannot be impersonated or you do not have permission."
I think it is because we can not impersonate a windows group. Am I right?