Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
+2 votes
4 views
in Azure by (45.3k points)

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?

4 Answers

+2 votes
by (16.8k points)
edited by

Check this syntax for granting the select permission:

USE YourDB;

GRANT SELECT ON dbo.functionName TO UserName;

If you wish, you can learn Learn Azure step by step or join Azure Administrator certification

The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
Intellipaat-community
by (44.4k points)
This granted the select permission and now it is working
by (47.2k points)
I think i had to uncheck 'db_denydatareader' and 'db_denydatawriter'. Thanks Shubham.
by (19.9k points)
This worked for me. Thank you.
+2 votes
by (107k points)

I think the problem is with the user having denied privileges. This error comes when the user which you have created does not have enough privileges to access your tables in the database. You should have to grant the privilege to the user to get what you want.

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

The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
Intellipaat-community
by (19.7k points)
It helped, well explained!
by (32.1k points)
Great! This answer helped in solving the problem!
Thanks!
+2 votes
by (29.5k points)

try following the steps given below

  •     Start SQL Management Studio.
  •     Expand the Server Node (in the 'Object Explorer').
  •     Expand the Databases Node and then expand the specific Database which you are trying to access using the specific user.
  •     Expand the Users node under the Security node for the database.
  •     Right click the specific user and click 'properties'. You will get a dialog box.
  •     Make sure the user is a member of the db_owner group and other required changes using the view.
The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
Intellipaat-community
by (29.3k points)
This worked for me. thanks!
by (41.4k points)
Be careful about what you are supposed to put into the db_owner database role.
0 votes
by (106k points)

You can use the syntax to grant select permission is to let user test access the database XXX see the code below:-

USE YourDB; 

GRANT SELECT ON dbo.functionName TO UserName;

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...