How to deny a user to access (select, insert, delete, update) to tables but grant (select) a few

Question

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;

Answer 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.