How to create an Azure SQL Database user who can CREATE/ALTER/DROP views but not tables, can read data but not INSERT/UPDATE/DELETE/TRUNCATE data?

Question

Is it possible to create an Azure SQL Database user who can:

• SELECT against all tables and views
• CREATE/ALTER/DROP views

But the user should NOT have the below permissions:

• INSERT/UPDATE/DELETE/TRUNCATE TABLE against any table or view
• CREATE/ALTER/DROP any table

What is the proper statements to achieve the above requirements, or it is not possible in Azure SQL?

I tried the below statements, without the last statement, the user cannot create views, but adding the last statement the user can drop tables.

CREATE USER [TestUser] WITH PASSWORD=N'NAvCO_h2eMuX', DEFAULT_SCHEMA=[dbo];

CREATE ROLE [TestRole];

ALTER ROLE [TestRole] ADD MEMBER [TestUser];

ALTER ROLE [db_datareader] ADD MEMBER [TestRole];

GRANT CREATE VIEW TO [TestRole];

GRANT SELECT ON SCHEMA :: dbo TO [TestRole];

GRANT ALTER ON SCHEMA :: dbo TO [TestRole];

Answer 1

When you run the ALTER ROLE [db_datareader] ADD MEMBER [TestRole] or GRANT SELECT ON SCHEMA :: dbo TO [TestRole], the user [TestUser] will be the readonly role db_datareader of the database. You can not do any other opreations like "INSERT/UPDATE/DELETE against any table or view".

But you add the ALTER permission to [TestUser], the user both will have SELECT and ALTER permission, the user will be the role like db_owner. Off course it has the permission to create view or drop tables.

It's impossible to create an Azure SQL Database user which both have the readonly permission and CREATE VIEW permission.

Reference: Database-Level Roles

Hope this helps.

Answer 2

As a workaround, you can create a Database DDL Trigger that does not allow that user to create, drop or alter any table or specific tables.

ALTER TRIGGER [TR_ProtectTables]
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS

DECLARE @eventData XML,
               @uname NVARCHAR(50),
               @sname NVARCHAR(100),
               @oname NVARCHAR(100),
               @otext VARCHAR(MAX),
               @etype NVARCHAR(100),
               @edate DATETIME

SET @eventData = eventdata()

SELECT
        @edate=GETDATE(),
        @[email protected]('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
        @[email protected]('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
        @[email protected]('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
        @[email protected]('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)'),
        @[email protected]('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

IF @oname IN ('tblBananas','tblApples','tblOranges') and @uname = 'UserName'
  BEGIN
    DECLARE @err varchar(100)
    SET @err = 'Table ' + @sname + '.' + @oname  + ' is super duper protected and cannot be dropped.'
    RAISERROR (@err, 16, 1) ;
    ROLLBACK;
  END

GO

ENABLE TRIGGER [TR_ProtectTables] ON DATABASE
GO

To avoid the user can INSERT/UPDATE/DELETE any data you can create a Role for that and add the user to that role.

CREATE ROLE [DenyWriteOnly]

EXEC sp_addrolemember N'db_datareader', N'DenyWriteOnly'

--explicitly DENY access to writing

EXEC sp_addrolemember N'DB_DenyDataWriter', N'DenyWriteOnly'

--now add the user to the role

EXEC sp_addrolemember N'DenyWriteOnly', N'MyDomain\YourUser'

I see you have edited your original question and you want to prevent the truncate table also. The easiest is to prevent a user from truncating any table is to enable Change Data Capture, but that feature is available for Azure Managed Instance.

If you don't have Azure Managed Instance then solution will be more elaborated since the minimum permission required for a TRUNCATE is ALTER. The other options are making tables to participate on transactional replication, making tables part of indexed views, or creating an empty table simply for the purpose of creating a dummy foreign key on each table that reference the empty table.