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];

Also consider ALTER ROLE [db_denydatawriter] ADD MEMBER [TestUser] — Dai
– Dai, Commented Nov 12, 2019 at 5:16

Leon Yue · Accepted Answer · 2019-11-12 09:40:21Z

2

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.

edited Nov 12, 2019 at 9:40

answered Nov 12, 2019 at 9:33

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J.W Over a year ago

In some cases, we want to give the users permission to create their own views in databases (e.g., the reporting database), but they should not change any data or table structure. It is a shame that SQL Server doesn't have the ability to do this.

Leon Yue Over a year ago

@J.W No, SQL server doesn't support do that for now. If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you.

J.W Over a year ago

Hi Leon, Alberto's solution is very closed, which means there could be workaround to achieve this, please check out my reply to Alberto's latest update, thanks.

Alberto Morillo · Accepted Answer · 2019-11-15 17:55:49Z

1

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.

edited Nov 15, 2019 at 17:55

answered Nov 12, 2019 at 14:59

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

4 Comments

J.W Over a year ago

Hi Alberto, I guess it needs another trigger to protect data if don't want the user to INSERT/UPDATE/DELETE any data, could you update your answer to have this part please? Also I assume if the user knows how to drop the triggers this workaround won't work?

Alberto Morillo Over a year ago

A database role may do the job here and will help you avoid creating a trigger per table.

J.W Over a year ago

Hi Alberto, thanks for the update, tested your answers and they are very closed to meet the required permission control. I have edited my question to include deny TRUNCATE TABLE query, but your current answers have not cover this, the user still can truncate table, all other controls have achieved :)

J.W Over a year ago

Thanks for the update, from your reply there seems no simple solution to the required permission control in Azure SQL database. The other options mentioned in your answer is not easy to manage and also the super admin user need have the permission to truncate table.

Collectives™ on Stack Overflow

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?

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related