Azure / Azure/data-api-builder

[Bug]: DEFAULT values not applied if database user is no db_owner

Open
#1,360 2 comments 0 reactions 1 assignee Claimed by @Aniruddh25 View on GitHub
bug mssql triage
Dominant language
C#
Stars
1.5k
Forks
370
Avg merge
3d 17h
Merged PRs (30d)
8

Description

### What happened?

If the user used to connect to the database is not in the db_owner role, columns default values are not read. Here's the repo (on SQL Server).

Create user:

```sql
create login dab_read_only with password = 'XXXXX';
go
create user dab_read_only from login dab_read_only;
go
alter role db_datareader add member dab_read_only
alter role db_datawriter add member dab_read_only
go
```

Create table:

```sql
CREATE TABLE [dbo].[TestDefault]
(
[Id] [uniqueidentifier] NOT NULL,
[NoDefault] [nvarchar](1000) NOT NULL,
[Default1] [bit] NOT NULL,
[Default2] [varchar](128) NOT NULL,
[Default3] [int] NOT NULL,
[Default4] [datetime2](0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
[Default5] [int] NOT NULL DEFAULT(200),
)
GO
ALTER TABLE [dbo].[TestDefault] ADD PRIMARY KEY NONCLUSTERED
(
[id] ASC
)
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ((0)) FOR [Default1]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT ('public') FOR [Default2]
GO
ALTER TABLE [dbo].[TestDefault] ADD DEFAULT (100) FOR [Default3]
GO
```

Create the configuration file with the following commands:

```
dab init --database-type mssql --connection-string 'Server=tcp:localhost,1433;Initial Catalog=DAB_TestDefault;Persist Security Info=False;User ID=dab_read_only;Password=XXXXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;'
dab add TestDefault --source "dbo.TestDefault" --permissions "anonymous:*"
```

then start DAB and try to issue a POST request, for example:

```http
POST https://localhost:5001/api/TestDefault
content-type: application/json

{
"NoDefault": "This has no default"
}
```

will return the following error

```json
{
"error": {
"code": "BadRequest",
"message": "Invalid request body. Missing field in body: Id.",
"status": 400
}
}
```

instead of correctly inserting the data. By adding the user to the `db_owner` role, the problem get fixed:

```sql
alter role db_owner add member dab_read_only
```

Which is weird since the data in the system view `` is available also to non db_owner.

```
select * from sys.default_constraints
```

### Version

0.5.34

### What database are you using?

Azure SQL

### What hosting model are you using?

Local (including CLI)

### Which API approach are you accessing DAB through?

REST, GraphQL

### Relevant log output

```Text
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
Route matched with {action = "Insert", controller = "Rest"}. Executing controller action with signature System.Threading.Tasks.Task`1[Microsoft.AspNetCore.Mvc.IActionResult] Insert(System.String) on controller Azure.DataApiBuilder.Service.Controllers.RestController (Azure.DataApiBuilder.Service).
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
5ed080e1-aa25-4130-9d74-37c40677f814: Invalid request body. Missing field in body: Id.
fail: Azure.DataApiBuilder.Service.Controllers.RestController[0]
5ed080e1-aa25-4130-9d74-37c40677f814: at Azure.DataApiBuilder.Service.Services.RequestValidator.ValidateColumn(ColumnDefinition column, String exposedName, IEnumerable`1 fieldsInRequestBody, Boolean isReplacementUpdate)
at Azure.DataApiBuilder.Service.Services.RequestValidator.ValidateInsertRequestContext(InsertRequestContext insertRequestCtx, ISqlMetadataProvider sqlMetadataProvider)
at Azure.DataApiBuilder.Service.Services.RestService.ExecuteAsync(String entityName, Operation operationType, String primaryKeyRoute)
at Azure.DataApiBuilder.Service.Controllers.RestController.HandleOperation(String route, Operation operationType)
```

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.