We are looking at using tSQLt for SQL Server Unit testing but prior to doing so we need to understand if we're going to be wasting our time trying to work with our set-up. The reason I ask this is because we have two databases, one of which is pretty "normal" (which is our production database) and the other which is an "API" database that provides restricted access to this database via stored procedures that "flip" between the user account that you access the API database in and an impersonated account for the production database. Tables in the production database are not accessed directly but via synonyms. Transactional provision is from the "entry point" stored procedure in the API database.
There are two main things that we want to be able to test;
(1) Security permissions of objects as we can run into issues when working with shared objects when we "forget" to set the security access up correctly between builds.
(2) API stored procedures / functions. I think we can test some of the functions relatively independently but I can't see how the stored procedures would easily work.
Does tSQLt support security permission testing ?
Indirectly. You can use the ExpectException and ExpectNoException features to test whether or not a given account has permissions to access a particular object.
Synonyms
AFAIK you still can't fake a synonym that points to a remote object. You might have more luck faking views though.
Why does tSQLt require CLR permissions ?
If you mean TRUSTWORTY, this is no longer required (http://tsqlt.org/748/tsqlt-v1-0-5873-27393-release-notes/). If you mean "Why does it require CLR at all", this is because there are some operations that are very hard or impossible to do with T-SQL by itself; have a look at the earlier tsqlunit for a brave attempt.
Does the tSQLt "code" have to be installed on the database it's going to test against
Yes.
What I would add is that if you are testing the interactions between two databases, the department of hair-splitting distinctions might be along to say that this isn't really unit testing. It might be easier to write a plain old NUnit/MSTest/whateverUnit test. I have had some luck in the past with Nunit and Dapper (of stackoverflow fame) in putting together lightweight database tests.