Required permission to use tSQLt on SQL Server

Question

Is there any other way to run tSQLt on a SQL Server database without sysadmin role or ALTER TRACE permission? We are currently trying to use a test tool called SQLTest from Redgate which uses the tSQLt framework. We have installed it successfully on the database with the sysadmin role, but no one is able to use the tool apart from the person with the sysadmin role. Anyone else who tries gets an error message relating to permission. I've been in touch with Redgate support and all they tell me is that the sysadmin role is needed or at least ALTER TRACE permission. These are elevated permissions and shouldn't be given to all users on a database.
Any help would be appreciated.

Answer 1

It is not clear exactly what your use case is but...

Typically, developers would use SQLTest and/or tSQLt on a local sandbox e.g. SQL Server Developer Edition installed on their laptops. If that is the case, most orgs should have no problems allowing developers to be sysadmin on their own locally installed SQL Server instance.

If you are using this on a shared SQL Server instance, again this should be a DEV environment where, hopefully SQL devs are allowed to administer their own dev environment.

I can't imagine any org allowing developers sysadmin access in a production instance but then you really shouldn't be using tSQLt in production anyway.

Answer 2

I might be wrong, but I believe it's only db_owner that's required to run tSQLt tests. Might depend on what procedures you're testing. Almost all of our tests are just data quality AssertTableEmpty tests.

If you have the Redgate Toolbelt (or SQL Compare, specifically), you should be able to create personal dev databases on the fly, make your changes, run the tests, then SQL Compare back to shared dev db.

Answer 3

There are two things at play here. tSQLt requires the executing principal to have permissions to alter any object in the database. Usually, you achieve that by making them part of the db_owner role.

Redgate SQLTest requires (in some circumstances) additional permissions. For example, the ALTER TRACE permission is needed to execute the code coverage tool that comes with SQLTest.

In addition to the above, there is a file called prepareserver.sql that needs to be executed once per SQL SERVER (not per database). It requires sysadmin privileges.

So, if you cannot use dedicated environments as @datacentricity recommended, you can execute tSQLt directly and not use SQLTest, or figure out which options to disable if you want to use SQLTest.

Like others here, I strongly recommend you use dedicated environments to do your development, independent of tSQLt.