SQL Server 2008 R2 – Fix User Can’t View Database Contents in SSMS

sql-server-2008-r2ssms

I have a particular user, let's call it TheUser. This user is able to connect to my SQL Server instance and view a list of the databases. However, upon expanding a database to view tables, stored procedures etc. I am greeted with this lovely, vague message:

enter image description here

This isn't being written to the error log, so I can't actually get a more detailed error (unless I'm looking in the wrong place?) TheUser's default database is currently set to master. If I set it to anything else, I'm unable to login. It has also been granted connect permissions under the database's permissions. Does anyone know how to resolve this?

Best Answer

To clear things up, language-wise: In SQL Server, the login is global for the SQL Server instance (a "server principal") and allows a client to log on to the server with very limited permissions.

Once you've set up a login, you can assign users to this login. A user is the login's security principal in a specific database ("database principal"), which means that a login can have multiple users, one for each database where he holds permissions or role memberships.

You might have added a login, but not the user, or you may not have assigned the user enough permissions in the database. To view the definition of database objects, your user will probably need VIEW DEFINITION, either on the database, the schema or on the individual object. These permissions are inherited. If you want to be able to read the data of tables, add SELECT permissions. Membership in the database role db_datareader gives the user complete read-only access to all tables and views in the database.

I've recently written a series of blog posts on SQL Server security that might give you a little more details.

Related Question