Azure SQL Database – User Cannot Select from Table with SELECT Permissions

azure-sql-databasepermissionsrole

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?

Best Answer

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.

Related Question