0

i am trying to deny a user to access (select, insert, delete, update) to tables on my database but i also need him to grant only select to a few tables, to do this i created a user with login and gave him datareader permission then by using the code below i Denied user to Select data from a table,

DENY SELECT ON "dbo"."Customer" TO "test"

He can't see the table which is fine but he can still select the data by

select * from Customer

what i want is to deny select data from table, i don't care whether he sees the table.

for further step i will deny users to access tables but few views.

here is the image; screen shot

not owner

system user

for sepupic

  • That user should not be able to select from the table. Are you sure he is selecting from dbo.Customer and not a table with the same name but a different schema (not dbo). – Randi Vertongen Nov 28 '19 at 8:45
  • i am sure, there is a test database which has only one schema and also user has access only to that database. – washaq Nov 28 '19 at 8:50
  • 1
    Under this user execute the following code: select * from sys.fn_my_permissions('your_table', 'object'), what permissions does it show? – sepupic Nov 28 '19 at 9:01
  • ...And why do you enclose everything in double quotes??? – sepupic Nov 28 '19 at 9:03
  • 1
    Your user is sysadmin and there is no way to deny to him – sepupic Nov 28 '19 at 9:18
1

The "secret" here is that your login test is sysadmin and he's mapped to dbo in every database. Test user that is on your picture is NOT the user corresponding to login "test".

If you want to execute under user test you should write the code

exec as login = 'test'
select *
from customer
revert

And this user will have the deny on customer table.

But now all the code you executed you executed it under dbo user (that corresponds to test login), this can be proved by executing

select system_user, user

that will show test as system_user and dbo as user.

This situation can happen if the login (test) was mapped to this database when he was not sysadmin, so test user was created. But then someone made him sysadmin and now he is mapped to dbo and test is just orphaned.

Another situation is when database was restored and that user (still orphaned) has no corresponding login, the new test login is not related to it and is mapped to dbo

how can i correct it ? it should be a just a user

exec sp_dropsrvrolemember 'test', 'sysadmin'

This way you make him an ordinary login.

Then you should check if your user test is his corresponding login or just re-map it:

alter user test with login = test;

UPDATE

Assuming that the first screenshot is shown "as is" it seems that the code to test "Check if it still works" was executed under the same account that denied the select permission, i.e. under sa login as there is no code impersonating test login. This explains because the select code was executed without errors and also the result of select system_user, user confirms this.

| improve this answer | |
  • Dear Sepupic i really appreciate your help, the thing is i created user, no one has access to my local database, so no one can change the ownership etc. and the user is not orphaned, many times i deleted and re-created user to understand what is wrong, when i created user i just gave him datareader permission nothing more but still no hope, i also run the commands you gave, it did not change anything. – washaq Nov 28 '19 at 9:41
  • @washaq please update your question with the result of select system_user, user (executed under the test login) – sepupic Nov 28 '19 at 9:45
  • it is still same as in system user image – washaq Nov 28 '19 at 9:48
  • In your image the code is executed as "sa". I asked you about system_user, user executed under test login – sepupic Nov 28 '19 at 9:51
  • @washaq SSMS shows your login in the status bar. Make sure you execute the code under test login. If SSMS still shows you "sa" execute the code with exec as login = 'test' – sepupic Nov 28 '19 at 9:54

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.