azure - Member of db_denydatawriter can still execute UPDATEs - Stack Overflow

admin管理员组

文章数量:1334342

I have an Azure SQL database server, with a database "test". In Azure Entra, I created user "[email protected]", and gave that user these roles on the database "test":

• db_datareader
• db_denydatawriter

I would expect user "[email protected]" to be able to execute SELECTs against tables in the database "test", but not UPDATEs, etc.

However, when I log into the database server as "[email protected]" and open a query window against database "test", it will let me execute UPDATEs against database tables just fine.

What do I need to do to make sure "[email protected]" cannot execute UPDATEs?

I have an Azure SQL database server, with a database "test". In Azure Entra, I created user "[email protected]", and gave that user these roles on the database "test":

• db_datareader
• db_denydatawriter

I would expect user "[email protected]" to be able to execute SELECTs against tables in the database "test", but not UPDATEs, etc.

However, when I log into the database server as "[email protected]" and open a query window against database "test", it will let me execute UPDATEs against database tables just fine.

What do I need to do to make sure "[email protected]" cannot execute UPDATEs?

• azure
• azure-sql-database
• microsoft-entra-id
• azure-entra-id

Share Improve this question Follow edited Nov 20, 2024 at 9:09 Thom A 96.1k1111 gold badges6161 silver badges9494 bronze badges asked Nov 20, 2024 at 9:02 user1147862user1147862 4,22688 gold badges3939 silver badges5656 bronze badges 3

• Is the account actually a LOGIN which is also a sysadmin? – Thom A Commented Nov 20, 2024 at 9:10
• @ThomA No, this account is not a LOGIN. It is only a USER in database "test". – user1147862 Commented Nov 21, 2024 at 5:30
• When that user connects, what does select user_name() return? – David Browne - Microsoft Commented Nov 21, 2024 at 15:47

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I faced the same issue even I have only db_datareader, db_denydatawriter roles to the user in my database as shown below:

I am able to execute update query against the table. In my case the user is server admin. That may be reason to the update execution. If the user is server admin then the roles are assigned at server level, according to the MS document

Azure SQL Database currently provides seven fixed server roles. The permissions that are granted to the fixed server roles can't be changed and these roles can't have other fixed roles as members.

So, you can't revoke update execution on the database, when user is server Admin. If you want to revoke update execution on the database change the server admin and create user in the database, if the user belongs to AAD user, login to database as Active directory admin, use below query to create user in required database and add above roles:

CREATE USER [AADUser] from external provider;
ALTER ROLE db_datareader ADD MEMBER [AADUser];
ALTER ROLE db_denydatawriter ADD MEMBER [AADUser];

Then you will be able to restrict the update execution as shown below:

本文标签： azureMember of dbdenydatawriter can still execute UPDATEsStack Overflow