Steve Jones - SSC Editor (11/2/2012)
Check that the Windows groups have logins assigned in SQL Server.
Check any other Windows groups that have logins for DENY permissions. NOTE that I hate DENY permissions for this reason.
If the Windows groups have logins, check that they are mapped to users in databases. If databases are restored, this can be broken.
Check the same thing for permissions and roles. MAke sure not DENYs exist.
fn_my_permissions allows you to check permissions. However if users cannot see databases, this is something else.
Thanks, Steve, for this help. I hadn't considered DENY or the fn_my_permissions.
Turns out the error in this case was the one described here, Error 916. I think it's because a database was set to AutoClose, which I hadn't noticed because it was a vendor-installed database. Apparently this is a bad option to enable, so I'm going to use a script to check for this setting on my other servers.
Thanks again for your help!
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
"Operator! Give me the number for 911!" - Homer Simpson
"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"