The "secret" here is that your
login test is
sysadmin and he's mapped to
dbo in every
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'
And this user will have the deny on
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
This situation can happen if the login (test) was mapped to this database when he was not
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
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;
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.