0

I'm setting up my database on an Azure SQL Server instance.

(I know the principal names are terrible, it's just a dry run test)
I created a login ("Test"), a user ("Test"), a schema ("Test"), added a table to the schema ("TestTable", created a role ("TestWriter"), set role permissions for the schema and added the user to the role.

I granted the role the following permissions:

GRANT 
    INSERT, 
    SELECT, 
    UPDATE
ON SCHEMA::Test
    TO TestWriter
GO

and denied all other permissions, following the practice of "least permissions" (i.e. giving a role/user only the minimum permissions it needs):

DENY 
    DELETE, 
    ALTER, 
    CONTROL, 
    EXECUTE, 
    REFERENCES, 
    TAKE OWNERSHIP, 
    VIEW DEFINITION 
ON SCHEMA::Test
    TO TestWriter
GO

however then there was an error selecting and/or inserting into the table in the schema, the error being

The INSERT permission was denied on the object 'TestTable', database 'TestDb', schema 'Test'.

through trial and error I found out the problem is fixed if I GRANT CONTROL permission to the role.
However, from what I read the CONTROL permission isn't something I'd want to grant willy-nilly to anyone who just needs to SELECT/INSERT into a table. Is there something I am missing? Is it possible to SELECT/INSERT/UPDATE a table WITHOUT having CONTROL permission?

1

You shouldn't have to explicitly DENY any of those permissions since SQL Server is implicitly deny-first, meaning a Login / User / Security Principal (such as a Role) has no access to anything until you've explicitly granted access (either via scripting it with T-SQL using the GRANT keyword or using the UI that SSMS provides). Explicitly denying a permission takes precedent even over an explicit GRANT.

Think of it this way, there are three states a permission can be in and the order of precedence (by highest precedence first) for permissions in SQL Server work like this: Explicit DENY > Explicit GRANT > Implicit deny-first (default)

By explicitly granting CONTROL, you're not solving the problem or understanding the root is, rather you're just sidestepping it with a workaround by granting a very wide permission set (the explicit GRANT here supercedes the implicit deny-first nature).

Outside of that, I'm unsure of what your root issue actually is, but I'd recommend by first revoking (not granting) all of the explicit DENY permissions you've mapped to the TestWriter role, and start testing what your Test user has access to.

4
  • I took your advice and revoked all deny permissions. Also tested it with revoking the CONTROL only and it works. Which just puzzles me even more, since NOT grating control it should be implicitly denied but it works but when explicitly denying it - it doesn't. Apr 6 at 19:49
  • @user14092802 I'm not an expert on SQL Server permissions unfortunately, I just know high level how they work. My guess would be that since SQL Server follows deny-first security patterns, when you DENY CONTROL it effectively cascadingly explicitly denies all permissions under the CONTROL permission, which as you read, is a lot of things. And even if you explicitly GRANT one of those things back to a User, it still is explicitly denied (Explicit Deny > Explicit Grant). But when you leave everything unpermissioned and in its default state of implicitly denied and then...
    – J.D.
    Apr 6 at 19:57
  • ...you explicitly GRANT the granular permissions you want, then the system should work as you need (Explicit Grant > Implicit Deny).
    – J.D.
    Apr 6 at 19:58
  • Yeah, I guess it's something like that. Apr 7 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.