Options

Required permission to use SQL Test on SQL Server database

MWnewbieMWnewbie Posts: 5 New member
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.

Answers

  • Options
    Ben_PBen_P Posts: 225 Silver 2
    Hi @MWnewbie,

    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.
  • Options
    MWnewbieMWnewbie Posts: 5 New member
    Thanks Ben_P. I'll go through the documentation and see how far I get.
  • Options
    MWnewbieMWnewbie Posts: 5 New member
    Hi @Ben_P,
    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.



  • Options
    Ben_PBen_P Posts: 225 Silver 2
    This looks like it's being caused by a permissions issue, the login needs to either be a member of the sysadm role or at least have the ALTER TRACE permission.
  • Options
    MWnewbieMWnewbie Posts: 5 New member
    I'll check this with the OPS guys who might have more permissions than I do. I'm not able to give these roles on a database level.
  • Options
    MWnewbieMWnewbie Posts: 5 New member
    @Ben_P
    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?
  • Options
    Ben_PBen_P Posts: 225 Silver 2

    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:

     

    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 ---

     

    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

Sign In or Register to comment.