DENYed user wasn't denied - is this a parallel query processing issue?

53 views Asked by At

I'm experiencing with permissions in SQL Server, and followed along the lines in this tutorial:

  1. Creating a Login and a User,
  2. Then GRANTing the user a SELECT permission
  3. Testing a SELECT query while impersonating as that user
  4. REVERT, and then DENYing that user the SELECT permission
  5. Again impersonating as that user and proofing SELECT doesn't work.

On step number 5, I executed the query:

EXECUTE AS USER = 'MyDeniedUser';
GO
SELECT * FROM Products;

And to my surprise the query worked, though it shouldn't have been. I tried executing it again several times, and it still worked.

Then, I added some random query to see if something changes (I used SELECT DB_NAME(); before the EXECUTE AS command), and now I got the expected error - the query didn't work.

What caused this behavior? (My first guess was that this is a parallel query processing issue, but the GO should've made the execution order as needed).

EDIT: This behavior happened again. I executed:

REVERT;
GO
DROP LOGIN MyDeniedLogin;  
DROP USER MyDeniedUser;  
GO

Then tested to see if the MyDeniedLogin still exists - and it did. I than added some dummy command (I used SELECT CURRENT_USER; GO) after the first GO, and it worked.

EDIT2: Full original script (without the aforementioned "dummy" commands):

-- Set up a login and user
CREATE LOGIN DenyTestServerLevel WITH PASSWORD = 'DenyTest', 
     CHECK_POLICY = OFF;
GO
USE NORTHWND;
GO
CREATE USER DenyTestDBLevel FROM LOGIN DenyTestServerLevel;
GO

-- Set up a role that's granted SELECT permissions to the database
CREATE ROLE GrantSelectRole;
GRANT SELECT TO GrantSelectRole;
EXEC sp_addrolemember 'GrantSelectRole','DenyTestDBLevel';
EXECUTE AS USER = 'DenyTestDBLevel';
GO
-- This should work:
SELECT * FROM Products;

-- revert back to the 'dbo' user:
REVERT;
GO
-- and deny:
DENY SELECT TO DenyTestDBLevel;
GO

EXECUTE AS USER = 'DenyTestDBLevel';
GO
-- Now this shouldn't work:
SELECT * FROM Products;

-- revert back and drop everything:
REVERT;
GO

DROP LOGIN DenyTestServerLevel;  
DROP USER DenyTestDBLevel; 
DROP ROLE GrantSelectRole
GO
0

There are 0 answers