I'm experiencing with permissions in SQL Server, and followed along the lines in this tutorial:
- Creating a Login and a User,
- Then GRANTing the user a SELECT permission
- Testing a SELECT query while impersonating as that user
- REVERT, and then DENYing that user the SELECT permission
- 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