Accessing Database object throws error when using SMO with App Role

Question

I am using SMO to control DB objects for my application. We have an optional feature which allows user to specify the application role credentials to allow them to manage the database access for the application using the app role.

Our SMO implementation works fine if I don't turn on the application role. However, when using the application role, I see following when I try to iterate over the list of tables. I see that the error occurs when internally SMO library tries to execute sql such as "Use <myDB>" even if the database object itself is the correct DB (e.g. <myDB>).

Here is the line in code that throws the error where myDB is of type "Microsoft.SqlServer.Management.Smo.Database".

var alltables = myDB.Tables.Cast<Table>().ToList();

Error: The current user account was invoked with SETUSER or SP_SETAPPROLE. Changing databases is not allowed.

Here is the callstack when the exception is thrown.

System.Data.dll!System.Data.SqlClient.SqlConnection.OnError(System.Data.SqlClient.SqlException exception, bool breakConnection, System.Action<System.Action> wrapCloseInAction) Line 1644 C# System.Data.dll!System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(System.Data.SqlClient.TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose) Line 1271 C# System.Data.dll!System.Data.SqlClient.TdsParser.TryRun(System.Data.SqlClient.RunBehavior runBehavior, System.Data.SqlClient.SqlCommand cmdHandler, System.Data.SqlClient.SqlDataReader dataStream, System.Data.SqlClient.BulkCopySimpleResultSet bulkCopyHandler, System.Data.SqlClient.TdsParserStateObject stateObj, out bool dataReady) Line 2061 C# System.Data.dll!System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(string methodName, bool async, int timeout, bool asyncWrite) Line 3173 C# System.Data.dll!System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(System.Threading.Tasks.TaskCompletionSource<object> completion, string methodName, bool sendToPipe, int timeout, out bool usedCache, bool asyncWrite, bool inRetry) Line 1678 C# System.Data.dll!System.Data.SqlClient.SqlCommand.ExecuteNonQuery() Line 1230 C# Microsoft.SqlServer.ConnectionInfo.dll!Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSqlAction action, object execObject, System.Data.DataSet fillDataSet, bool catchException) Line 598 C# Microsoft.SqlServer.ConnectionInfo.dll!Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(string sqlCommand, Microsoft.SqlServer.Management.Common.ExecutionTypes executionType, bool retry) Line 775 C# Microsoft.SqlServer.SqlEnum.dll!Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteImmediate(string query) Line 162 C# Microsoft.SqlServer.SqlEnum.dll!Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(System.Collections.Specialized.StringCollection query, object con, Microsoft.SqlServer.Management.Smo.StatementBuilder sb, Microsoft.SqlServer.Management.Smo.DataProvider.RetriveMode rm) Line 308 C# Microsoft.SqlServer.SqlEnum.dll!Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(Microsoft.SqlServer.Management.Sdk.Sfc.ResultType resultType, System.Collections.Specialized.StringCollection sql, object connectionInfo, Microsoft.SqlServer.Management.Smo.StatementBuilder sb) Line 623 C# Microsoft.SqlServer.SqlEnum.dll!Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(Microsoft.SqlServer.Management.Smo.SqlEnumResult sqlresult, Microsoft.SqlServer.Management.Sdk.Sfc.ResultType resultType) Line 599 C# Microsoft.SqlServer.SqlEnum.dll!Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(Microsoft.SqlServer.Management.Sdk.Sfc.EnumResult result) Line 572 C# Microsoft.SqlServer.Management.Sdk.Sfc.dll!Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData() Unknown Microsoft.SqlServer.Management.Sdk.Sfc.dll!Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Microsoft.SqlServer.Management.Sdk.Sfc.Request req, object ci) Unknown Microsoft.SqlServer.Management.Sdk.Sfc.dll!Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(object connectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc.Request request) Unknown Microsoft.SqlServer.Smo.dll!Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Microsoft.SqlServer.Management.Sdk.Sfc.Request req) Unknown Microsoft.SqlServer.Smo.dll!Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Microsoft.SqlServer.Management.Sdk.Sfc.Urn levelFilter, Microsoft.SqlServer.Management.Smo.ScriptingPreferences sp, bool forScripting, System.Collections.Generic.IEnumerable<string> extraFields) Unknown Microsoft.SqlServer.Smo.dll!Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(bool refresh, Microsoft.SqlServer.Management.Smo.ScriptingPreferences sp, string filterQuery, System.Collections.Generic.IEnumerable<string> extraFields) Unknown Microsoft.SqlServer.Smo.dll!Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetEnumerator(Microsoft.SqlServer.Management.Smo.ScriptingPreferences sp) Unknown System.Core.dll!System.Linq.Enumerable.CastIterator<Microsoft.SqlServer.Management.Smo.Table>(System.Collections.IEnumerable source) Line 2548 C# mscorlib.dll!System.Collections.Generic.List<Microsoft.SqlServer.Management.Smo.Table>.List(System.Collections.Generic.IEnumerable<Microsoft.SqlServer.Management.Smo.Table> collection) Unknown

em.Core.dll!System.Linq.Enumerable.ToList<Microsoft.SqlServer.Management.Smo.Table>(System.Collections.Generic.IEnumerable<Microsoft.SqlServer.Management.Smo.Table> source) Line 2272	C#

MIAC.SchemaManager.dll!MIAC.MIACSchemaManager.MSSQL.SQLSchemaManager.SQLSchemaManager(string connectionString, string productName, MIAC.MIACSchemaManager.SchemaUpdateOptions opts) Line 80 C# MIAC.SchemaManager.dll!MIAC.MIACSchemaManager.SchemaManager.SchemaManager(string dbConnectionString, MIAC.MIACSchemaManager.DBType dbType, string productName, int targetVersion, MIAC.MIACSchemaManager.SchemaUpdateOptions opts) Line 46 C# SchemaManagerTests.dll!MIAC.SchemaManagerTests.SQLTests.SQLCleanUpdateTest21() Line 236 C# [Native to Managed Transition] [Managed to Native Transition] Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestMethodRunner.DefaultTestMethodInvoke(object[] args) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestMethodRunner.RunTestMethod() Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestMethodRunner.ExecuteTest() Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestMethodRunner.ExecuteInternal() Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestMethodRunner.Execute() Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.UnitTestRunner.RunInternal(Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestMethod testMethod, bool isDataDriven, System.Collections.Generic.Dictionary<string, object> runParameters) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.MSTestFramework.UnitTestRunner.RunSingleTest(string name, string fullClassName, bool isAsync, System.Collections.Generic.Dictionary<string, object> runParameters) Unknown [AppDomain (testhost.net48.exe, #1) -> AppDomain (UnitTestAdapter: Running test, #3)] Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.ExecuteTestViaMsTestV1Runner(Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase testCase, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.ITestExecutionRecorder testExecutionRecorder, Microsoft.VisualStudio.TestPlatform.MSTestFramework.UnitTestRunner testRunner, out System.DateTimeOffset startTime, out System.DateTimeOffset endTime) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.ExecuteTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.ITestExecutionRecorder testExecutionRecorder, Microsoft.VisualStudio.TestPlatform.MSTestFramework.UnitTestRunner testRunner, Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.TmiTestRun tmiTestRun) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.ExecuteDurontoTestsInternal(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.ITestExecutionRecorder testExecutionRecorder, string source, Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestRunDirectories runDirectories, Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.TmiTestRun tmiTestRun, int globalTestTimeout) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.ExecuteDurontoTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle, string source, Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestRunDirectories runDirectories, Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.TmiTestRun tmiTestRun, int globalTestTimeout) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.ExecuteTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle, Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestRunDirectories runDirectories, bool isDeploymentDone, string source, int globalTestimeout) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.ExecuteTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle, Microsoft.VisualStudio.TestPlatform.MSTestFramework.TestRunDirectories runDirectories, bool isDeploymentDone, int globalTestTimeout) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MixedModeExecutor.RunTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle, Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.TestRunCancellationToken cancellationToken) Unknown Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.dll!Microsoft.VisualStudio.TestPlatform.Extensions.VSTestIntegration.MSTestExecutor.RunTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle) Unknown Microsoft.VisualStudio.TestPlatform.Common.dll!Microsoft.VisualStudio.TestPlatform.Common.ExtensionDecorators.SerialTestRunDecorator.RunTests(System.Collections.Generic.IEnumerable<Microsoft.VisualStudio.TestPlatform.ObjectModel.TestCase> tests, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IRunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle) Unknown Microsoft.TestPlatform.CrossPlatEngine.dll!Microsoft.VisualStudio.TestPlatform.CrossPlatEngine.Execution.RunTestsWithTests.InvokeExecutor(Microsoft.VisualStudio.TestPlatform.Common.ExtensionFramework.Utilities.LazyExtension<Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.ITestExecutor, Microsoft.VisualStudio.TestPlatform.Common.Interfaces.ITestExecutorCapabilities> executor, System.Tuple<System.Uri, string> executorUri, Microsoft.VisualStudio.TestPlatform.CrossPlatEngine.Adapter.RunContext runContext, Microsoft.VisualStudio.TestPlatform.ObjectModel.Adapter.IFrameworkHandle frameworkHandle) Unknown Microsoft.TestPlatform.CrossPlatEngine.dll!Microsoft.VisualStudio.TestPlatform.CrossPlatEngine.Execution.BaseRunTests.RunTestInternalWithExecutors.AnonymousMethod__0() Unknown Microsoft.TestPlatform.PlatformAbstractions.dll!Microsoft.VisualStudio.TestPlatform.PlatformAbstractions.PlatformThread.Run.AnonymousMethod__0() Unknown mscorlib.dll!System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx) Unknown mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state, bool preserveSyncCtx) Unknown mscorlib.dll!System.Threading.ExecutionContext.Run(System.Threading.ExecutionContext executionContext, System.Threading.ContextCallback callback, object state) Unknown mscorlib.dll!System.Threading.ThreadHelper.ThreadStart() Unknown

Changing databases is not allowed

Answer 1

Looks like you have hit a roadblock as far as application roles are concerned.

And that might just be as well. I guess this is a Windows application that sits on the user's desktop and connects directly to the database? For such applications, application roles are not particularly safe, since the user has access to the password. Yeah, you may have hidden it somewhere, but it's only security by obscurity. The application runs with the user's credentials, so if the application can find the password, so can the user.

If you want users to be able to perform privileged actions, the best solution is to package the operation in a stored procedure that you sign with a certificate, and then you create a user for that certificate and grant that user the permissions needed. I have an article on my web site where I describe this technique in detail: https://www.sommarskog.se/grantperm.html. The article also includes a chapter where I discuss application roles and similar techniques.

Yes, I realise that this means a re-architecture for you, since you cannot use SMO for the operations.