user Databases not accessible to non-sa logins
-
I have a user with a non-sa login to SQL Server 2005. He can connect to the server with SSMS but cannot see any user databases. He can open the system tables but when he clicks on the user ones he gets dialog box saying "the database is not accessible."
I know for a fact the databases are accessible! I am a member of sysadmin and can see them and I tested giving his account sysadmin and he could see the databases during that test.
He is a member of the db_datareader role of all user databases so he can do select queries. (I also temporarily made him a member of db_owner but that didn't help so I removed it.) The box was locked down according to the DISA STIG - I'm wondering if some permission somewhere was taken away for non-SAs.
I really cannot give him SA access just so he can do queries! Any ideas? Thanks
-
Check the properties on the user databases, specifically on the Options page, under Stat: Restrict Access. If this is set to RESTRICTED_USER, that will explain why he can't access the databases. This should be set to MULTI_USER.
-
Thanks - I checked and the property was already set to Multiple.
-
The only other thing I can think of is deleting the servers from SSMS and register them with his username and password. It sounds like a permissions issue with SQL. I haven't run into this problem where I work, so I can't think of any other possibilities that may be causing this. His being able to see the system tables suggests he has at least public access to the server and the databases.
Sorry I can't think of anything else at the moment.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply