[CT-2044] Secondary role in tests

### Is this your first time submitting a feature request?

- [X] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [X] I have searched the existing issues, and I could not find an existing issue for this feature
- [X] I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

In my project I'm using a secondary role as a pre-hook, which is working fine. But the problem is, that the pre-hook is not working for tests. I have experieced a similar problem in some macros (from dbt_utils), which need to access tables related with the secondary role. Unfortunately I need to merge the previleges of both roles now, which is not always that easy in big companies. I hope, I haven't missed any solution, that already exists.

### Describe alternatives you've considered

_No response_

### Who will this benefit?

Anybody, who is forced to use a secondary role.

### Are you interested in contributing this feature?

I think, I'm not able.

### Anything else?

_No response_