The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'

Question

I moved a database from SQL Server 2012 to Azure. I don't want to use the user master, so I created a user test. This is what I did for database XXX on Azure:

create user test from login test with default_schema=[dbo]
exec sp_addrolemember 'db_owner','test'

I checked, and the database objects I am interested in are all in the schema dbo. The table Users is in the schema dbo.

The connection string in my web project has test as the login. It produces the error message:

The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'

What does the error message mean and what can I do to let user test access the database XXX?

Answer 1

1. Open SQL Management Studio
2. Expand your database
3. Expand the "Security" Folder
4. Expand "Users"
5. Right click the user (the one that's trying to perform the query) and select Properties.
6. Select page Membership.

7. Make sure you uncheck

   db_denydatareader

   db_denydatawriter

This should go without saying, but only grant the permissions to what the user needs. An easy lazy fix is to check db_owner like I have, but this is not the best security practice.

Answer 2

I think the problem is with the user having deny privileges. This error comes when the user which you have created does not have the sufficient privileges to access your tables in the database. Do grant the privilege to the user in order to get what you want.

GRANT the user specific permissions such as SELECT, INSERT, UPDATE and DELETE on tables in that database.

Answer 3

The syntax to grant select permission on a specific table :

USE YourDB;

GRANT SELECT ON dbo.functionName TO UserName;

To grant the select permission on all tables in the database:

USE YourDB;

GRANT SELECT TO UserName;

Answer 4

This is how I was able to solve the problem when I faced it

1. Start SQL Management Studio.
2. Expand the Server Node (in the 'Object Explorer').
3. Expand the Databases Node and then expand the specific Database which you are trying to access using the specific user.
4. Expand the Users node under the Security node for the database.
5. Right click the specific user and click 'properties'. You will get a dialog box.
6. Make sure the user is a member of the db_owner group (please read the comments below before you use go this path) and other required changes using the view. (I used this for 2016. Not sure how the specific dialog look like in other version and hence not being specific)

Answer 5

Grant permissions for that user is needed

Answer 6

Check space of your database.this error comes when space increased compare to space given to database.

Answer 7

Using SSMS, I made sure the user had connect permissions on both the database and ReportServer.

On the specific database being queried, under properties, I mapped their credentials and enabled datareader and public permissions. Also, as others have stated-I made sure there were no denyread/denywrite boxes selected.

I did not want to enable db ownership when for their reports since they only needed to have select permissions.

Answer 8

I had just, for the first time, used the Plesk panel on my website hosting service to download an "Export Dump" file of my hosted SQL Server database.

After needing to use a different StackOverflow Question/Answer to understand how to use what I had downloaded, then in SQL Server Management studio I went into Security | Logins and pointed one of the Logins to the new local database I now had and tried to run my app. I got the error message in this Question (except my error message mentioned 'Keys' rather than 'Users').

So then, I followed the Answer provided here by Kellen Stuart, and it was a help for sure. The difference in my situation was, instead of the login having all the Database roles checked, my login had none of the roles checked.

So for my purposes (developing an app on my local workstation) I just needed to check db_owner, and I was good to go.

Answer 9

as others said you need to set a grant for your user and tables and in conclusion for your database. you have to solve your problem with UI as bellow image. with this technic you must set some permissions(CRUD) to your specific user and finally your user can use that really easy.

The SELECT permission was denied on the object 'Users', database 'Xyz', schema 'dbo' ;

Answer 10

I resolve my problem doing this. [IMPORTANT NOTE: It allows escalated (expanded) privileges to the particular account, possibly more than are needed for individual scenario].

1. Go to 'Object Explorer' of SQL Management Studio.
2. Expand Security, then Login.
3. Select the user you are working with, then right click and select Properties Windows.
4. In Select a Page, Go to Server Roles
5. Click on sysadmin and save.

Answer 11

May be your Plesk panel or other panel subscription has been expired....please check subscription End.