0

I have a situation where I have a table in a SQL Server database, and this table has a trigger that, upon a user deleting a record, transfers that record to another database on the same server. However, I'm facing an issue where if the user doesn't exist on the target database, it results in an access error.

I'd like to find a solution that doesn't involve creating user accounts for each user on the target database since the number of users is quite extensive.

Is there a way to handle this scenario gracefully without encountering access errors when transferring records between databases in SQL Server?

Improve this question
5
  • You could create the change record on the DB in a staging table, then have an SSIS /SQL job transfer from that table to the other DB on a schedule. Commented Oct 23, 2023 at 14:16
  • 2
    Ideally, don't have a cross-database solution. If the objects were both in the same database, then you could make use of permission chaining. Alternatively, why not use a temporal table, if you need historical versions of the data? Commented Oct 23, 2023 at 14:18
  • I think that using module signing would be a viable alternative to needing to enable cross-database permission chaining. There's a small overhead of needing to re-sign the trigger whenever it's altered but since you've already got the trigger in source control (right?), you can add the add signature statement alongside the trigger definition. Commented Oct 23, 2023 at 15:36
  • I'm not using "tmp" (temporary) because I want the data to persist even after the session is closed. If you store data in a temporary table or database, it will typically be cleared automatically when the session ends. If you want the data to be available across sessions, it's better to use a regular table or a more persistent storage method.@thoma a Commented Oct 24, 2023 at 5:25
  • You mentioned "Ownership Chaining." Ownership chaining is a security feature in SQL Server that allows objects within the same database to access each other's data without explicitly granting permissions. However, this feature doesn't extend to objects in different databases. So, if you need data to be accessible across databases, you might need to consider other methods like cross-database queries, linked servers, or replication, depending on your specific use case. If you have more specific questions or need further assistance, please feel free to ask. Commented Oct 24, 2023 at 5:26
Related questions
4342
How do I UPDATE from a SELECT in SQL Server?
3327
How to add a column with a default value to an existing table in SQL Server?
2450
How to concatenate text from multiple rows into a single text string in SQL Server
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.