Sql-server – .NET SQL Server Authentication Schema issue

migrationschemasql server

I am currently working on a project for a client. The application is written in ASP.NET C#. The Database used is SQL Server 2008. The project has been to migrate the old Oracle database to a new SQL Server database so it connects to the .NET code and works correctly. We used Microsoft Microsoft SQL Server Migration Assistant (SSMA) to convert the Oracle database across to SQL server. This worked for the most part, however we did have to make some changed to the stored procedures and some functions.

The problem is that now when i try and run the .NET code it will not pick up any of the stored procedures or functions as they now have a schema prefix and the .NET code does not pick this up.

The .NET code connects to the SQL Server database using Windowws Authentication and therefore i am unable to add a default schema to the user or login within SQL Server as it is a group.

Is there any way i can get around this?

If you would like more information i will be happy to provide it.

Thanks

Connection string

<add key="DatabaseConnectionString" value="Data Source=DATASOURCE;Initial Catalog=DATABASE;Integrated Security=true;"/>

Stored Procedure Call

// Open a connection to the database
            SqlConnection databaseConnection = GetDatabaseConnection();

            // Initialise the SQL command object
            SqlCommand cmd = new SqlCommand("PKG_PACKAGE_NAME$PR_PROCEDURE_NAME",
               databaseConnection);
            cmd.CommandType = CommandType.StoredProcedure;

Boldonglen

Best Answer

I'm not sure what is preventing you assigning a default schema to the user. You can simply use:

ALTER USER [NT AUTHORITY\Authenticated Users] 
WITH DEFAULT_SCHEMA = [Production]

Obviously replacing the user/schema names as appropriate.

However, it would be best if you could update the code to use a schema prefix - some more reading on that is here (functional reasons) and here (performance reasons).

Related Question