SQL Execute AS userA vs Login as userA

I have a tableA that disallow userA to update it. (userA is a DB user)

DENY UPDATE ON tableA TO userA;

When I login with Window Authentication, I update tableA with below query.

[Query]:

EXECUTE AS USER = 'userA';
GO
update tableA set colA='0001' where id=1;
GO 
REVERT;

[Message]:

The UPDATE permission was denied on the object 'tableA', database 'DW', schema 'dbo'.

====> This is my expected result.

However, when I changed to login as 'userA' with SQL Server Authentication.

[Query]:

update tableA set colA='0001' where id=1

====> It allowed to update! It is supposed to block from updating, right? May I know why it does not block it? Anything I have missed out?

1 answer

  • answered 2018-01-20 09:07 Razvan Socol

    This may happen if the login userA is a member of the sysadmin server role.

    To get the actual behavior of this login, you should impersonate the login instead of the user:

    EXECUTE AS LOGIN='userA'
    

    When you impersonate the user, only the rights explicitly granted to the user (in the current database) are considered. When you impersonate the login, also the server-level rights are considered.

    If a login is a member of the sysadmin server role, the permissions (including any explicit DENY) are not verified anymore. According to https://docs.microsoft.com/en-us/sql/t-sql/statements/deny-transact-sql, "DENY does not apply to object owners or members of the sysadmin fixed server role".