Options
Required permission to use SQL Test on SQL Server database
I would like to know what permission/rights need to be given to users on a SQL Server database in order to be able to use an installation of SQL Test on the database.
This is with SSMS.
This is with SSMS.
Answers
https://documentation.red-gate.com/sqt/adding-a-database-to-sql-test describes the steps that SQL Test takes on installing the framework, which is the part which needs elevated permissions.
You should be able to have someone with the appropriate permissions install the framework onto the database, after which the developers just need to be able to create and execute stored procedures and CLR in the appropriate schemas.
After the installation on the database, one user tries to run a test and gets the following permissions error message -
SQLCover.SqlCoverException: SQL Cover failed to start. ---> System.InvalidOperationException: Error creating the extended events trace, error: User does not have permission to perform this action. ---> System.Data.SqlClient.SqlException: User does not have permission to perform this action. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SQLCover.Gateway.CommandWrapper.b__5_0() at SQLCover.Gateway.CommandWrapper.OpenConnectionAndDo[T](Func`1 func) at SQLCover.Gateway.CommandWrapper.ExecuteNonQuery() at SQLCover.Gateway.DatabaseGateway.Execute(String query) at SQLCover.Trace.TraceController.RunScript(String query, String error) --- End of inner exception stack trace --- at SQLCover.Trace.TraceController.RunScript(String query, String error) at SQLCover.Trace.AzureTraceController.Create() at SQLCover.Trace.AzureTraceController.Start() at SQLCover.CodeCoverage.Start() --- End of inner exception stack trace ---
Do you have any ideas about what might be missing?
Another user logs on to the database but when they go to add the database in SQLTest, they are unable to make a selection as the 'ok' button is greyed out.
These are elevated permissions, from a security perspective, just to be able to run and create a test case. Is there any other way to run SQL Test where the user is not an admin on the database?
SQL Test is basically a bunch of Stored Procedures and Functions that make use of the tSQLt framework. For the initial install, sysadmin permissions are required to enable the CLR.
Once the initial install is completed, the user will require privileges to execute the SQL Test stored procedures, SQL Test functions and the database objects being tested.
As for the error being reported here:
The user will require ALTER TRACE permissions for creating the extended trace events that the SQL Cover component requires for obtaining a full breakdown of coverage of each function / stored procedures.
https://documentation.red-gate.com/sqt/test-coverage-with-sql-cover