Managing Write Access for Business Users in Motherduck with Service Account-Owned Databases

·Mar 12, 2026 03:50 PM·

I've got a use case that is proving difficult in Motherduck. I have a service account that runs my dbt packages, creating tables across 6 different databases. I have shared these databases with some business users, which works great for read only work. However, some of these users have a need to write (running ad hoc scripts that create tables or insert into tables) which can't be done on their shares, and they can't write to the main databases because they are owned by the service account. The only workaround I can think of is to issue the user a token with access to the underlying databases and then have them connect with something like Datagrip or the cli to execute their procedures. Am I missing something fundamental here, or is this the intended way that this work should be accomplished?

👀1

12 comments

· Sorted by Oldest

Yahia M.
·
·
Hi Adam, Shares in MotherDuck are read-only, and only the owning account can write to a database. There's no way for multiple accounts to have concurrent write access to the same database today. A possible workaround is to create a service account per user, then create a copy of the shared databases into each service account so they have their own read-write copies for ad hoc work. More flexible access controls are something we're actively thinking about . If you'd like, I can connect you with one of our customer engineers to talk through the best setup for your use case. Just let me know!
Adam R.
·
·
Thanks. It's good to know that there are more flexible access controls in the works. The workaround seems ok but then we're juggling all kinds of databases and I feel like people will get confused and governance will suffer massively.
Yahia M.
·
·
That makes sense. Managing those extra databases adds complexity. Looping in Blossom C. to help find the right setup for your team.
Blossom C.
·
·
Hi Adam R. would you like to get on a call to provide more detail on your use case?
Jacob M.
·
·
Blossom C. would love to be looped on this when you connect
👍1
Jacob M.
·
·
I wonder if the blessed-dives style service account for CI would work here
Adam R.
·
·
Hi there, yes let's chat. What's the best way to find a good time?
Blossom C.
·
·
Adam R. would any of the these times work for you (all times in EDT) Mon 2:30pm-3pm Tue 11:30am-12pm Wed 4pm-4:30pm
MarkYourData
·
·
Blossom C. same here, would like to learn what would be the best practice for ad hoc writes by data analysts. From my perspective, the users would not write to curated DBT datasets. A personal ad hoc (maybe even temp) dataset that is not visible to others maybe?
Adam R.
·
·
Blossom C. the only time of those that works for me is Monday 2:30-3. Others are totally welcome to join. My use case is very simple: analysts doing ad hoc SQL work that generate business outcomes (basically running small models) which we want to capture the output and metadata about in prod.
Blossom C.
·
·
Adam R. great i'll send an invite
Blossom C.
·
·
Adam R. Tomorrow we're hosting a webinar: Agents That Build Tables, Not Just Query Them where we'll discuss how the new query_rw tool in the MotherDuck MCP server gives agents write access to your warehouse, so they can save derived tables and intermediate results. If you register you should be able to get the recording, in case you can't make it.

Yahia M.
·
·
Hi Adam, Shares in MotherDuck are read-only, and only the owning account can write to a database. There's no way for multiple accounts to have concurrent write access to the same database today. A possible workaround is to create a service account per user, then create a copy of the shared databases into each service account so they have their own read-write copies for ad hoc work. More flexible access controls are something we're actively thinking about . If you'd like, I can connect you with one of our customer engineers to talk through the best setup for your use case. Just let me know!
Adam R.
·
·
Thanks. It's good to know that there are more flexible access controls in the works. The workaround seems ok but then we're juggling all kinds of databases and I feel like people will get confused and governance will suffer massively.
Yahia M.
·
·
That makes sense. Managing those extra databases adds complexity. Looping in Blossom C. to help find the right setup for your team.
Blossom C.
·
·
Hi Adam R. would you like to get on a call to provide more detail on your use case?
Jacob M.
·
·
Blossom C. would love to be looped on this when you connect
👍1
Jacob M.
·
·
I wonder if the blessed-dives style service account for CI would work here
Adam R.
·
·
Hi there, yes let's chat. What's the best way to find a good time?
Blossom C.
·
·
Adam R. would any of the these times work for you (all times in EDT) Mon 2:30pm-3pm Tue 11:30am-12pm Wed 4pm-4:30pm
MarkYourData
·
·
Blossom C. same here, would like to learn what would be the best practice for ad hoc writes by data analysts. From my perspective, the users would not write to curated DBT datasets. A personal ad hoc (maybe even temp) dataset that is not visible to others maybe?
Adam R.
·
·
Blossom C. the only time of those that works for me is Monday 2:30-3. Others are totally welcome to join. My use case is very simple: analysts doing ad hoc SQL work that generate business outcomes (basically running small models) which we want to capture the output and metadata about in prod.
Blossom C.
·
·
Adam R. great i'll send an invite
Blossom C.
·
·
Adam R. Tomorrow we're hosting a webinar: Agents That Build Tables, Not Just Query Them where we'll discuss how the new query_rw tool in the MotherDuck MCP server gives agents write access to your warehouse, so they can save derived tables and intermediate results. If you register you should be able to get the recording, in case you can't make it.

Managing Write Access for Business Users in Motherduck with Service Account-Owned Databases | MotherDuck Community