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?

  • 1
    This isn't necessarily a permissions problem. Are you sure the database is online? Is the database set to AutoClose or currently in single_user or restricted_user? Have you looked at any of the columns in sys.databases to see if they're different from other databases the user can access? – Aaron Bertrand Aug 25 '14 at 21:32
  • 1
    Also, security-related error messages are vague on purpose. Imagine if you stole someone's credit card, and you tried to guess their pin, and you were off by one number - do you think anyone wants the ATM to announce that you were just off by one number? The less information you disclose, the harder it is for them to figure out if they're pursuing the right things. It is more important to protect you than make your job easier. – Aaron Bertrand Aug 25 '14 at 22:04
  • Can you connect to the database is a query window and run queries against the objects in the database? What version of SSMS are you using? – mrdenny Aug 26 '14 at 4:17

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.

| improve this answer | |
  • This is the closest to the solution, so I'll accept this. The login did not have appropriate roles set. – MGZero Aug 26 '14 at 12:41

Hi Please assign permission for that user and run following statement.

use database

grant select on view_name to login_name

after executing this command your problem will resolve.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.